Checking SQL Load by Top Queries

So you're checking you SQL server, you see the CPU is very high for long periods of time or your users complain the database is slow, where can you start looking?

Well, we can check the load and what is causing it.

The basic query is:

SELECT TOP 500
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time

FROM sys.dm_exec_query_stats
ORDER BY sys.dm_exec_query_stats.last_elapsed_time DESC

We can modify the order by or where clauses so it will give the results for our needs.

I would recommend adding 

where execution_count > 100

So it will weed out the single long-running queries from the result set or you can check for execution_count = 1 if you suspect a programmer abuses dynamic SQL.

Then we can modify the order by 

ORDER BY total_worker_time/execution_count desc

so it will give us the mostly used/long running queries. 

You can find out more in the documentation.

Tags: