HI all

Today received the mail from RED-GATE team on this topic “How to find CPU intensive queries” . It  is an interesting article and with good focus on DMV  and its use to find out expensive query . hence I  thought to share it .

High CPU usage is a common SQL Server problem. Fortunately, you can find queries with high CPU time using the sys.dm_exec_query_stats DMV, which was added in SQL Server 2008.

This DMV keeps performance statistics for cached query plans, so you can find the most expensive queries and query plans on your system.

Let’s have a look at how to do it.

Much of the information is stored in fields called “Total” or “Last” – for example, total_worker_time and last_worker_time – which give you the total cost for all executions of the query, and the cost of the last execution of the query.

total_worker_time can be used to find the query that uses the most CPU time (recorded in microseconds). This isn’t necessarily the most CPU intensive query, though – a high total_worker_time just means the query is being executed often, which may also be a problem.

To find the most CPU intensive query, use last_worker_time. Check the execution_count too, because the query may not be a problem if it only runs once or twice.

To get query itself, you’ll need a CROSS APPLY with the sys.dm_exec_sql_text DMF. This returns the text for the entire batch, however, while the rows in sys.dm_exec_query_stats keeps information about each query.

You can solve this with statement_start_offset and statement_end_offset, which pinpoint the position of the query inside the batch, plus a bit of string manipulation to retrieve the query text.

Last, you need to retrieve the query plan, so you can find the reason for high CPU consumption. You can do so using a CROSS APPLY with the sys.dm_exec_query_plan DMF.

Let’s see how it works – if you want a query with high CPU usage first, try this batch in a test environment  (download the scripts as a .zip):

— Clear the query plan cache (don’t execute this in production!)

DBCC FREEPROCCACHE

GO

— CPU intensive query over AdventureWorks database

SELECT   TransactionId ,

         ProductId ,

         ReferenceOrderId ,

         ReferenceOrderLineId ,

         TransactionDate ,

         TransactionType ,

         Quantity ,

         ActualCost ,

         ModifiedDate

FROM     production.TransactionHistory

ORDER BY modifiedDate DESC

Now, let’s check the most CPU intensive queries in our system:

SELECT  

— using statement_start_offset and

— statement_end_offset we get the query text

— from inside the entire batch

        SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,

         ( ( CASE qs.statement_end_offset

               WHEN -1 THEN DATALENGTH(qt.TEXT)

               ELSE qs.statement_end_offset

             END – qs.statement_start_offset ) / 2 ) + 1) AS [Text] ,

     qs.execution_count ,

     qs.total_logical_reads ,

     qs.last_logical_reads ,

     qs.total_logical_writes ,

     qs.last_logical_writes ,

     qs.total_worker_time ,

     qs.last_worker_time ,

— converting microseconds to seconds

     qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,

     qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,

     qs.last_execution_time ,

     qp.query_plan

FROM sys.dm_exec_query_stats qs

— Retrieve the query text

     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

        — Retrieve the query plan

     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC — CPU time