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.