Solved

LATCH_EX with CXPACKET causing query performance degradation

Posted on 2009-05-13
1
1,929 Views
Last Modified: 2012-06-27
SQL Server 2000 SP4 Build 2187. When runing simple SELECT query with INNER joins causing LATCH_EX waittype with huge number of CXPACKET causing consistent high CPU with performance degradation. Indexes are in place with stats updated. Moving data files to different disk didn't make any difference. Execution plans are changing with and without 'TOP' clause. With 'TOP' clause plans are generating with 'INDEX SEEK, Nested Loop' while without 'TOP' clause changing to 'INDEX SCAN, Table Spool, Hash Match, Merge Join'. WHY? Can anyone give some action plan?
0
Comment
Question by:info2rafiq
1 Comment
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24381764
Hi info2rafig,  

sounds like some I/O problems maybe...

Are you running profiler by chance ? http://support.microsoft.com/kb/929728

There is a fairly high level overview of wait types and what to look for : http://www.sqldev.net/misc/waittypes.htm

A fella by the name of Thomas Davidson is the guru on 2000 performance. There is a whitepaper floating around, but his presentation is a bit more graphic : http://www.sqldev.net/events.htm#SQL_Connections_12-15_October_2003,_Palm_Springs_CA_  look for the one in the middle : SPR-201-4

Apologies about all the links, but performance is a minefield potentially, and best to understand what might be causing it first. So, have a look and read, and then come back with some more questions if needed - OK ?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question