Have you ever wondered which queries are causing the most strain on your SQL Server? In this blog post, we will discuss a simple script that can help you identify the most expensive queries running on your SQL Server box.

The script we will be using is quite basic, but it gets the job done effectively. Although there are many different versions available online, this script provides a straightforward solution to finding the most expensive queries.

SELECT TOP 10 
    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_logical_reads, 
    qs.last_logical_reads,
    qs.total_logical_writes, 
    qs.last_logical_writes,
    qs.total_worker_time,
    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 qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

The above script utilizes the sys.dm_exec_query_stats dynamic management view to gather information about query execution statistics. It retrieves details such as the query text, execution count, logical reads, logical writes, worker time, elapsed time, and the query plan.

By ordering the results based on different parameters, such as logical reads, logical writes, or CPU time, you can gain insights into the queries that are consuming the most resources.

It’s important to note that this script is valid for any version of SQL Server from SQL Server 2005 onwards. It has been extensively used in performance tuning consultations and has proven to be reliable.

If you have your own scripts or variations of this script, I encourage you to share them in the comments section below. It’s always interesting to see different approaches to solving the same problem.

Understanding which queries are the most expensive can help you optimize your SQL Server’s performance and improve overall efficiency. By identifying and addressing these resource-intensive queries, you can ensure a smoother and more responsive database environment.

Thank you for reading! Stay tuned for more SQL Server tips and tricks.