August 13, 2003 at 6:08 am
Running SQL Server 2000 sp3 on 2000 Professional. All documentation I can find states CX Packet Wait Type should not be happening in SQL 2000 but I am seeing this happen all the time and until thoses session are stopped performance is significantly decreased. Any thoughts why I am receiving this in SQL 2000.
thanks for any suggestions
AHF
August 13, 2003 at 6:40 am
AHF,
I've gotten those as well in the recent weeks running the latest version of SQL 2000 and the only work around I found was to use the OPTION (MAXDOP 1) to disable parralellism.
August 14, 2003 at 1:49 am
CXPacket locks occur when SPID is waiting for all threads to complete.
Reported as a bug in SQL Server 2000, SP1.
However, later SP's don't seem to solve this problem either.
August 14, 2003 at 3:28 am
Waiting for CXpacket is normal behaviour for a parallelized query. Appearently there was a in the past, where one(!) of the indications was waiting on the type: the real problem was in the underlying sort.
It makes sense to look at the query plan, and try to figure out what those subqueries/queryslaves or whatever you want to call them are doing for this waiting thread.
If overall performance degrades, the you might run into other bottlenecks: for instance if those parallel queries run on the same disk (or small set of disks) a disk bottleneck is easily reached..
Or they simply block other threads from doing work..
BTW, most of the reference to a 'cxpacket' problem refer to the same Q&A which refer to the 'bug' but it also notes that a subquery might be waiting for 'traditional' reasons: in which I read: no bug..
good luck,
Mario
May 23, 2008 at 11:09 am
Mbrood,
Im facing similar kind of issue where a job is running for a very long time and when checked the process it shows that the waittype for the CXPacket is waiting for I/O. And also there was no i/o errors reported.
The process was showing in suspended status and there was no blockings on server.
In this case how to find really where the bottleneck is? OR is that we can do clear the cache for the particular query and recompile once again?....
Let me know some ideas....Thanx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply