• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1984
  • Last Modified:

LATCH_EX with CXPACKET causing query performance degradation

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
info2rafiq
Asked:
info2rafiq
1 Solution
 
Mark WillsTopic AdvisorCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now