SQL 2005 Performance Tuning using DMVs - Part 3 of 7 - Alleviating CPU Pressure
This is the third installment on my series on using Dynamic Views to help with SQL 2005 performance tuning. The previous post on Identifying CPU Pressure can be found here.
After identifying that a database is cpu constrained, the next step is to find out what is using all the cpu. The first step is to identify the SQL batches that are using the most CPU. To find these batches, you use dm_exec_query_stats and join to dm_exec_sql_text to get the actual SQL statement. The following query will yield the top 10 (currently cached) sql statements for cpu utilization.
SELECT
derived_table.total_cpu_time,
derived_table.total_execution_count,
derived_table.total_cpu_time/derived_table.total_execution_count as cpu_per_execution,
derived_table.number_of_statements,
Plans.query_plan
FROM
(
SELECT TOP 10
SUM(qs.total_worker_time) as total_cpu_time,
SUM(qs.execution_count) as total_execution_count,
COUNT(*) as number_of_statements,
qs.plan_handle
FROM
sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) desc) derived_table
CROSS APPLY sys.dm_exec_query_plan(plan_handle) as Plans
order by derived_table.total_cpu_time/derived_table.total_execution_count desc
Another common cause of CPU over-utilization on a database server is query optimization time. If you take periodic snapshots of the dm_exec_query_optimizer_info DMV, you can get a good idea of the amount of time spent optimizing. The other way to look at optimization time is to examine how often queries are recompiled. The following SQL is useful for this examination:
--To examine queries that recompile often,
--plan_generation_num indicates the number of times
--the query has recompiled.
--The following sample query gives you the top 10 stored procedures
--that have been recompiled.
SELECT top 10
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
Another common cause of high CPU database server is queries that require parallel processing. You can use a combination of dm_exec_requests, dm_os_tasks and dm_exec_sessions to find all active requests utilizing parallel processing.
SELECT
r.session_id,
r.request_id,
MAX(ISNULL(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
FROM
sys.dm_exec_requests r
INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id
INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
WHERE
s.is_user_process = 0x1
GROUP BY
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
HAVING MAX(ISNULL(exec_context_id, 0)) > 0
Alternatively, you can just identify all cached plans that allow parallel execution.
SELECT
p.*,
q.*,
cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
WHERE
cp.cacheobjtype = 'Compiled Plan'
AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
This should help you identify and address the causes of high CPU usage. Next installment will take a look at identifying I/O bottlenecks. You can download the source for these examples here.