Finding slow queries in SQL Server

When you want to optimise your app, one of the most important things is to find your slowest database queries, to see if indexes are missing, or the queries should be rewritten. SQL Server stores statistics for its queries in the dynamic management view dm_exec_query_stats. In the book “TSQL Querying” by Itzik Ben-Gan, Itzik suggests the following as a good query to extract and format the information. This query sums the total elapsed time for queries of the same type:

select top 10
     max(query) as sample_query,
     sum(execution_count) as cnt,
     sum(total_worker_time) as cpu,
     sum(total_physical_reads) as reads,
     sum(total_logical_reads) as logical_reads,
     sum(total_elapsed_time) as duration
from (select qs.*,
               substring(st.text,(qs.statement_start_offset/2)+1,
		((case statement_end_offset when - 1 
                 then datalength(st.text) 
                 else qs.statement_end_offset end 
                 - qs.statement_start_offset)/2) + 1)
               as query
               from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_plan_attributes(qs.plan_handle) as pa
where pa.attribute = 'dbid'
and pa.value= db_id('your-database')
) as d
group by query_hash
order by duration desc

If you look on the web, you will sometimes see variations on this query that attempt to filter by calling the db_name() function on the database id stored in the statement. However, this will only work for stored procedures. The database name will be null for a normal SQL statement. This makes sense, because running the same statement against different databases, even if they have the same tables, could result in completely different query plans. Hence it is the query plan that is linked to the database. As you can see from the above, you can safely filter by converting the dbid stored in the plan attributes.

This entry was posted in Performance, SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

502,541 Spambots Blocked by Simple Comments

HTML tags are not allowed.