1. SELECT
  2. qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
  3. qs.total_elapsed_time / 1000000.0 AS total_seconds,
  4. qs.execution_count,
  5. SUBSTRING (qt.text,qs.statement_start_offset/2,
  6. (CASE WHEN qs.statement_end_offset = -1
  7. THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
  8. ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
  9. o.name AS object_name,
  10. DB_NAME(qt.dbid) AS database_name
  11. FROM sys.dm_exec_query_stats qs
  12. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  13. LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
  14. WHERE qt.dbid = DB_ID()
  15. ORDER BY average_seconds DESC;