June 12, 2008 at 9:40 am
Problem:
Client Server applications and COM+ applications have UNACCEPTABLE response times when using a specific database server(DB SERVER1).
Specific of DB SERVER 1:
I have a 8 way (3.00 Ghz) SQL SERVER 2000 box with Hyper Threading turned on
I have 32 GIG (2.99 Ghz) of RAM
Local Disks
RAID173GBx220GB OS
RAID5300GBx4836GB Backups
Array
RAID1300GBx2 278GB SQL
RAID0300GB 278GB TempDB
RAID10 300GBx6 836GB DB1
RAID10300GBx4 557GB Logs
When I run the following command (select * from master..sysprocesses) to discern what makes up my CPU usage, 60-75% of the SPIDs have a Wait Type or Last Wait Type of PAGELATCH_EX, PAGELATCH_UP, CXPACKET and NETWORKIO. If I sort by "CPU used" PAGELATCH_EX and CXPACKET dominate the CPU time.
DBCC SQLPERF (Waitstats) confirms that PAGELATCH_EX and CXPACKET again have the highest Wait Times. We redo our stats every morning at 7:00am.
The Server that has MAX DEGREE of PARALLELISM set to 32. It seems to me that this should be set to 8.
I hope to get some guidance on ways to decrease the WaitTimes on this server.
THanks for your Help.
June 12, 2008 at 10:59 am
I agree with you. Your MaxDop should be 8 or less.
As to the main question. I'd run a trace out to a file and start working on the longest running or most frequently called queries based on the data collected.
However, if you want to focus directly on the wait states as your best approach to identify performance issues, and that's a great approach, you should read this white paper from Microsoft.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2008 at 11:33 am
PAGELATCH_EX indicated waits for physical I/O
CXPACKET indicates waits for parallel processes to complete.
NETWORKIO indicates waits for Network I/O
For NETWORKIO, use perfmon to determine the workload and if there is sufficient thruput available.
For CXPACKET, if this is primarily OLTP transactions, suggest disabling parallelism (max degree of 1 ).
For PAGELATCH_EX, suggest you gather database file I/O statistics on a regular basis to determine if there is a disk resource problem:
select *
, IoStallMS / ( NumberReads + NumberWrites ) as MsPerIo
from :: fn_virtualfilestats(default,default)
The statistics are since the SQL Server was started, so you will need to determine the changes between two sets of statistics yourself. MsPerIo should be under 8 and under 4 is desirable. Also use perfmon to check the disk queue length.
I second Grant Fritchey's recommendation to run a trace. There are MS provided tools to analyze the traces that are not very friendly (obtuse command line switches ) but the results are incredibly useful and almost impossible to produce manually.
http://support.microsoft.com/kb/944837
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply