I'm seeing CXPACKET wait types in one of my database running SQL2005 which causing the CPU hit 100%. The server is 64 bit Windows 2003 with 8 CPU. Several tables contain over 10 million rows. Does anyone encounter this issue before and what was done to fix the problem?
When you started this question, you didn't provide what you have looked at, such as is your disk fragmented? index fragmented?
Did you query DMV for the top CPU sessions like so? (What do you see?) Once picked up the CPU culpit, did you interrigate the query efficiency with index scheme? I can go on and on ..... You have to check into a specific culpit, we can then advise you a solution.
WITH QPLAN AS
SELECT TOP 10 SUM(QS.TOTAL_WORKER_TIME) AS
TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
TOTAL_EXECUTION_COUNT, SUM(QS.TOTAL_WORKER_TIME)/SUM(QS.EXECUTION_COUNT) AS EACHEXECUTION, COUNT(*) AS
QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
QS CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE
ORDER BY SUM(QS.TOTAL_WORKER_TIME) DESC
SELECT * from QPLAN CROSS APPLY sys.dm_exec_query_plan(QPLAN.PLAN_HANDLE)