Sunday, March 25, 2012

Cursor Faster in a TEST Server than on a PROD Server

Hi,
I'm running a cursor (I know not quit good...but). For I don't know why, in one night the execution time step from 1h30 to 3h. I restore my database on a TEST Server (same version), the the execution time is still 1h30...
I monitored both execution, and realise that the CPU time for the CXPACKET process is 4 time higher it the PRODUCTION server than on the TEST Server....
Do you think that can be the problem?
From Neil Pike's FAQ
Q. What does a wait type of CXPacket or Exchange mean?
A. You will get this only with versions of SQL that support parallel queries, i.e. SQL 7 and later. It means that one thread of the query is waiting for a message packet from another, and the one it is waiting on is either blocked by a traditional
cause or has hit some sort of parallelism bug.
CXPacket means it is waiting on a data packet - i.e. the results of an internal query is being passed. Exchange means that it is waiting on a control packet - i.e. waiting for a child/sibling process to tell you that it is finished.
If the query doesn't complete then make sure the latest service pack is applied as there are several parallel query fixes in each one. If it still doesn't fix it then you have run into an unfixed bug and will need to contact Microsoft PSS and raise a bug
report. You should be able to work-around the problem by adding (MAXDOP=1) as a query hint which will prevent the query being parallelized.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

No comments:

Post a Comment