SQL 2000 - WAITSTATS: CXPACKET query

Question

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!

Answer 1

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

Answer 2

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