Tuesday, July 04, 2006

Most used indexes in SQL Server 2005

Question:
How to find mostly used indexes in SQL Server 2005?

Gabbar:

SELECT TOP 5 total_worker_time/execution_count 'Avg CPU Time',
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1)statement_text
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY
total_worker_time/execution_count DESC

This is copied from http://www.databasejournal.com/features/mssql/article.php/3608296

No comments: