WITH DB_CPU_Stats AS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num], DatabaseName, [CPU_Time_Ms], CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databasesAND DatabaseID <> 32767 -- ResourceDBORDER BY row_num OPTION (RECOMPILE);
After running this query with my customer, we saw that there were about five queries that were noticeably higher in total CPU than the rest.
Once we identified the highest CPU consumers, we started reviewing their execution plan by clicking on the link in our results.** There are a number of items to look for in any execution plan that could indicate where performance can be improved:
High cost operations
Index scans
Multiple executions of scans/seeks
Bookmark Lookups
Operations with very high rowcount
Sort operations
Implicit conversions
Estimated rows/executions that do not match actual rows/executions (which could indicated out of date statistics)
In this case, we found the execution plans for each didn’t have obvious red flags, like expensive scans. So, we reviewed the index seeks with higher costs. We reviewed the queries to see what columns were used in the WHERE and JOIN clauses. Using sp_help against the tables, we looked at the existing indexes and found that the indexes seemed to support these queries relatively well.