When it comes to performance tuning in SQL Server, one of the first questions that arises is: which queries are causing the most strain on the server? It is important to identify the queries that are consuming excessive resources, such as memory, CPU, or IO, in order to optimize their performance.

There are various methods to identify resource-intensive queries, including the use of Extended Events. However, in this article, we will focus on a simple approach using Dynamic Management Views (DMV).

It is important to note that DMVs only provide information from the existing cache. Therefore, if a query is not cached or has been explicitly removed from the cache, it will not be included in the results obtained from the DMV. Additionally, queries may age and be removed from the cache, especially if the cache size is limited.

Here is an example script that can be used to identify resource-intensive queries:

SELECT TOP 10
    qs.creation_time,
    qs.execution_count,
    qs.total_worker_time AS TotalCPU,
    qs.total_logical_reads + qs.total_logical_writes AS TotalIO,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS SQLText
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
    qs.total_worker_time DESC;

This script retrieves the top 10 queries based on their resource usage, including CPU time and IO operations. It also provides the SQL text of each query for further analysis.

It is important to exercise caution when running this script during business hours, especially if the cache size is large, as it can itself be resource-intensive.

Performance tuning is a complex subject, and there is no one-size-fits-all approach. However, identifying and optimizing resource-intensive queries is a crucial step in improving overall SQL Server performance.

For more tips and insights on SQL Server performance tuning, be sure to check out our other articles on SQL in Sixty Seconds.

Related articles:

• Simple Example to Configure Resource Governor – Introduction to Resource Governor
• SQL SERVER – DMV – sys.dm_exec_query_optimizer_info – Statistics of Optimizer
• SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28