CPU. One of the most frequent contributors to high CPU consumption is stored procedure recompilation. Here is a DMV that displays the list of the top 25 recompilations:
SELECT TOP 25 SQL_TEXT.TEXT, SQL_HANDLE, PLAN_GENERATION_NUM, EXECUTION_COUNT, DBID, OBJECTID FROM SYS.DM_EXEC_QUERY_STATS A CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT WHERE PLAN_GENERATION_NUM >1 ORDER BY PLAN_GENERATION_NUM DESC --This DMV lists the top CPU consumers: SELECT TOP 50 SUM(QS.TOTAL_WORKER_TIME) AS TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS TOTAL_EXECUTION_COUNT, COUNT(*) AS NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT, 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 Other things that cause high CPU usage are bookmark lookups, bad parallelism and looping code.
