Top Queries DMV

I spoke at the Pacific Northwest SQL Server User Group last night, and we covered Performance tuning. I promised to blog a script that uses the "query_stats" DMV and a CROSS APPLY to find the top queries - there are lots of these on the web, so here's one I like for I/O:

   1: SELECT TOP 10 
   2:        total_logical_reads,
   3:        total_logical_writes, 
   4:        execution_count,
   5:        total_logical_reads+total_logical_writes AS [IO_total],
   6:         st.text AS query_text,
   7:        db_name(st.dbid) AS database_name,
   8:        st.objectid AS object_id
   9: FROM sys.dm_exec_query_stats  qs
  10: CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
  11: WHERE total_logical_reads+total_logical_writes > 0 
  12: ORDER BY [IO_total] DESC

Comments (0)

Skip to main content