info2rafiq
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.