High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time.<\/p>\n
Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us to find the queries and query plans that are most harming our system.<\/p>\n
This DMV keeps some useful statistic information about the queries. All the values are stored in fields called “Total” and “Last”, this means we have the total value for all executions of the query and the value of the last execution of the query. For example, we have total_worker_time and last_worker_time.<\/p>\n
Total_worker_time field can be used to find the most harmful query in the system, but this doesn’t mean this is the most CPU intensive query, this means this query is executed too many times. To find the most CPU intensive query we can use last_worker_time, but this query would not be a problem if it was executed only one or two times.<\/p>\n
We can retrieve the query statement using a cross apply with sys.dm_exec_sql_text DMF, but this DMF will return the text for the entire batch, while each row in sys.dm_exec_query_stats keeps information about only one query. To solve this difference we have the fields statement_start_offset and statement_end_offset that points the exact position of the query inside the batch. We will need to do some string manipulation to retrieve the query text.<\/p>\n
For last, but not least, we need to retrieve the query plan, so we can analyze the query plan and find the reason of the high CPU consumption. To do so, we need to do a cross apply with the sys.dm_exec_query_plan DMF.<\/p>\n
To test this query, you can execute the following batch in a test environment to produce one query with high cpu usage:<\/p>\n
-- Clear the query plan cache (don't execute this in production!)\r\ndbcc freeproccache\r\ngo\r\n\r\n-- CPU intensive query over adventureworks database\r\nselect TransactionId,ProductId,\r\n ReferenceOrderId,ReferenceOrderLineId,\r\n TransactionDate,TransactionType,\r\n Quantity,ActualCost,\r\n ModifiedDate\r\nfrom production.TransactionHistory\r\n order by modifiedDate desc<\/pre>\nNow, let’s check the most cpu intensive queries in our system:<\/p>\n
SELECT\r\n -- using statement_start_offset and\r\n -- statement_end_offset we get the query text\r\n -- from inside the entire batch\r\n SUBSTRING(qt.TEXT, (qs.statement_start_offset\/2)+1,\r\n ((CASE qs.statement_end_offset\r\n WHEN -1 THEN DATALENGTH(qt.TEXT)\r\n ELSE qs.statement_end_offset\r\n END\r\n - qs.statement_start_offset)\/2)+1)\r\n as [Text],\r\nqs.execution_count,\r\nqs.total_logical_reads, qs.last_logical_reads,\r\nqs.total_logical_writes, qs.last_logical_writes,\r\nqs.total_worker_time,\r\nqs.last_worker_time,\r\n-- converting microseconds to seconds\r\nqs.total_elapsed_time\/1000000 total_elapsed_time_in_S,\r\nqs.last_elapsed_time\/1000000 last_elapsed_time_in_S,\r\nqs.last_execution_time,\r\nqp.query_plan\r\nFROM sys.dm_exec_query_stats qs\r\n -- Retrieve the query text\r\n CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt\r\n -- Retrieve the query plan\r\n CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp\r\nORDER BY qs.total_worker_time DESC -- CPU time<\/pre>\n","protected":false},"excerpt":{"rendered":"High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time. Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6810],"class_list":["post-8755","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8755","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8755"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8755\/revisions"}],"predecessor-version":[{"id":66417,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8755\/revisions\/66417"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8755"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}