SQL Server: Get I/O intensive and CPU intensive queries

Firstly runs some Perfmon and identify if you have a CPU issue or an I/O issue.

Then find the most I/O intensive queries using the following query.

SELECT TOP(100)CONVERT(VARCHAR,(total_logical_reads/execution_count)) as avg_logical_reads,
       CONVERT(VARCHAR,(total_logical_writes/execution_count)) as avg_logical_writes,
       CONVERT(VARCHAR,(total_physical_reads/execution_count)) as avg_physical_reads,
       CONVERT(VARCHAR,execution_count) as execution_count,
       (SELECT SUBSTRING(TEXT, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX),TEXT)) * 2
                ELSE statement_end_offset
            END - statement_start_offset)/2) as Query
     FROM sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_query_stats
ORDER BY avg_physical_reads DESC;

Then find the most CPU intensive queries using the following query.

SELECT 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), 

qs.execution_count, qs.total_worker_time,
qs.total_worker_time/qs.execution_count as avg_worker_time, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.last_worker_time, 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

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC -- CPU time


Make sure you analyze the data carefully. The execution count is a really important number. So check both the 'total_worker_time' and avg_worker_time. You might have queries that run for 1 or 2 secs but may be they run several times. 


SQL Server Get I/O intensive queries
SQL Server Get CPU intensive queries.