Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Platform: SQL 2000 Enterprise with SP4, running on Windows 2003 Enterprise, 8 cores, 8GB RAM, and internal (!) storage. Databases are roughly 100GB in total.

Scenario: The waitstats are heavily skewed to hardware:

CXPACKET - 35.9%
WRITELOG - 18.4%
LCK_M_S - 13.2%
PAGEIOLATCH_SH - 10.9%
NETWORKIO - 10.4%
LATCH_EX - 5.6%
PAGEIOLATCH_EX - 2.6%
LCK_M_IX - 1.5%
LCK_M_IS - 1.2%
PAGELATCH_UP - 0.1%

I am ignoring the LATCH and LCK types for now, because we're moving to SAN shortly during a SQL 2005 upgrade, and the internal drives are heavily fragmented anyway.

However, I'm concerned about the CXPACKET type. In some cases I've had 80% wait times relating to this. Does the parallel execution plan setting need to be reviewed? (it is set to 5 at the moment). Will moving to SAN fix this as well? Should I even worry in the first place?

Thanks in advance!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

CXPACKET wait states are related to parallelism - it's the cost of splitting queries over multiple processors. Reporting queries can benefit from parallelism by dividing into smaller chunks between processors and amalgamating the results.

If your application is OLTP, you could experiment by setting the instance wide 'Max Degree of Parallelism' to 1 (only use one processor to execute queries) or higher. The default value is 0 (all processors).

http://msdn.microsoft.com/en-us/library/aa196725(SQL.80).aspx

share|improve this answer
    
I eventually went with Max Degree of Parallelism of 5, and parallel execution plan of 75. Everyone is happy now. –  user3914 Jun 30 '09 at 19:39

CXPACKET is also related to I/O so moving to SAN should at least atenuate the problem.

share|improve this answer
    
Thanks! I thought as much from the documentation, but it's not very clear (or I'm just being blond). –  user3914 Jun 17 '09 at 11:06
    
You're welcome. –  Paul Jun 17 '09 at 12:02
    
Can you explain how CXPacket waits are related to IO? –  GilaMonster Jun 17 '09 at 16:16
    
I can't see why relocating to SAN storage will change the CXPACKET wait states you're seeing? –  SuperCoolMoss Jun 27 '09 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.