How to catch performance decrease in sql 2000?

Last Post 28 May 2008 10:52 PM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
werstatyw
New Member
New Member

--
25 Mar 2008 07:47 AM
Good day, community.
I faced serious problem in my testing (but heavily loaded) environment.
I think that my SQL 2000 experiencing memory leak. What way could i discover who or what is the reason of it?
If it was some article in sqlmag, it will be great!
Any additional information please ask.
Kind regards, Alexandr.
SQLUSA
New Member
New Member

--
25 Mar 2008 07:55 AM
Before you do anything.

1. Dbreindex your entire database with FILL FACTOR 70.
2. Test again
3. If performance issue persist, turn on SQL profiler for sprocs/queries with duration over 2000, save to a table for analysis.

Kalman Toth, Business Intelligence Architect
SQL Server Performance Tuning Bootcamp - http://www.sqlusa.com/order2005highperformance
werstatyw
New Member
New Member

--
31 Mar 2008 06:59 AM
Good day everybody.

There are 2 instances of SQL 2000 machine.
And it turns out that the first one was limited in max memory and the second one is not.

There is a restoration procedure of one of the databse which runs every weekend and sometimes on business day (because of the failure on weekends), and this procedure use hand-written stored procedure which was created in order to clean personal information (balance for exanmple)
The person who created it said that sp isn't used the indexes. Now it has.
The performance problem appears only on business day, but the first one in QA with this query
"select memusage, loginame, nt_username, hostname, program_name
from sysprocesses order by memusage desc"
is NOT SQLAgent.
I think that there's something with this sp and normal business activity which concurrents, but still investigate the issue.


werstatyw
New Member
New Member

--
02 Apr 2008 06:42 AM
Pro Pete, thanks for your reply!
What script or tool should i use in order to catch the suspect process?
werstatyw
New Member
New Member

--
19 May 2008 04:23 AM
Thanks for you reply, Pro Pete.
Now i'm developing the script with the output of processes, whose data difference between starting and current running is more than 6 hours.
I've developed the cursor which returns any spid with condition mention above.
The question is how could i input this parametr to DBCC Inputbuffer in order to understand, what sp is running?
DBCC Inputbuffer isn't support variable as input, the error is "Parameter 1 is incorrect for this DBCC statement"
Is there any workaround?
SQLUSA
New Member
New Member

--
19 May 2008 11:38 PM
quote:

Originally posted by: werstatyw
The question is how could i input this parametr to DBCC Inputbuffer in order to understand, what sp is running?
DBCC Inputbuffer isn't support variable as input, the error is "Parameter 1 is incorrect for this DBCC statement"
Is there any workaround?


Here is a script for you: http://www.sqlservercentral.com/scr...ent/31802/

However, I beg to differ with you on the approach you are taking. Normal database operations cannot possibly cause "memory leak". That can only occur if you are running some other programs (apps, or 3rd party SW with bugs, antivirus) on the database server.

1. Can you give us your detailed server setup? HW, RAID-s, data files, log files, tempdb layout
2. Are your indexes in order? Missing indexes?
3. Do you have "read hog" (poorly designed) sprocs running? Monitor with SQL Profiler.
4. Is there long ( > 15 sec) blocking frequently? (Microsoft has a script for this). If yes, identify the blocker and ask development to reengineer it.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Tuning Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/


werstatyw
New Member
New Member

--
21 May 2008 07:43 AM
Thanks Kalman, i will prepare the answers shortly.
werstatyw
New Member
New Member

--
28 May 2008 10:30 PM
1. Here they are:

OS Name Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Intel
System Model S5000PAL
System Type X86-based PC
Processor x86 Family 15 Model 6 Stepping 4 GenuineIntel ~2992 Mhz - 8*Xeon
Total Physical Memory 6 137,80 MB
Available Physical Memory 297,45 MB
Total Virtual Memory 7,81 GB
Available Virtual Memory 2,35 GB
Page File Space 2,00 GB
Page File C:\pagefile.sys


All database file are placed on non-system disk e:\ which uses 7-disks RAID 5 (Fibre-channel 4 gbit).

I wonder what does it mean: tempdb layout? Please explain in more detail.

2. As i've understood correctly, should i run 'DBCC CHECKIDENT'?

3. I find a delicious KB-article which helps me to understand what to monitor in profiler.
http://support.microsoft.com/kb/224587/en-us

4. Unfortunately, i couldn't find this in MS KB. Could you help me with this script?

Regards, Alexandr.
SQLUSA
New Member
New Member

--
28 May 2008 10:52 PM
quote:

Originally posted by: werstatyw

All database file are placed on non-system disk e:\ which uses 7-disks RAID 5 (Fibre-channel 4 gbit).

I wonder what does it mean: tempdb layout? Please explain in more detail.

Regards, Alexandr.


For high performance you need to put transaction logs and tempdb on dedicated RAID1 drives.

Not sure about the CHECKINDENT question. DBCC checkdb is used for database health checking.

To monitor for slow performing queries/sprocs:

1. Select the standard(default) template.
2. Delete sessions & security audit events
3. Set duration to 5000 (5 sec - you can decrease later)
4. Save trace to a table in Monitor database (set one up) or to a file
5. Use T-SQL to analyze the trace table


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2008 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/highperformance


You are not authorized to post a reply.

Acceptable Use Policy