SQL Statistical information – Index usage, etc and also tips on how to find the most costly SQL Server queries using DMV’s

This is just an information for the users that might not know this feature in SQL Server Management Studio, eventhough I’m sure most of people should have known this feature

Basically SQL Server stores all the database usage information including index usage, disk usage, top transaction etc. *This information is not available from the backup file

1. Right click your database, Go to Reports, Go to Standard Reports and you can find all the out of the box reports

Clicking one of the report will give you detailed information – in this sample, I’m using Index Usage Statistics

How to find the top 20 most expensive queries by the total execution time, run this query against the database

 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 total_elapsed_time_inSeconds = --Converted from microseconds
 sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

-It will give you this result set

– Clicking the query_plan will give you detailed query plan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s