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

SELECT TOP 20
 qs.sql_handle,
 qs.execution_count,
 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 qs.total_worker_time/1000000,
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 qs.total_elapsed_time,
 total_elapsed_time_inSeconds = --Converted from microseconds
 qs.total_elapsed_time/1000000,
 st.text,
 qp.query_plan
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_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