1. /*
  2. sys.dm_exec_query_stats
  3.  
  4. Returns aggregate performance statistics for cached query plans in SQL Server 2012. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
  5.  
  6. Remarks
  7. Statistics in the view are updated when a query is completed.
  8. User required VIEW SERVER STATE permission on the server.
  9.  
  10. Reference
  11. http://msdn.microsoft.com/en-us/library/ms189741.aspx
  12. http://mssqlfun.com
  13. */
  14.  
  15.  
  16. --Top 10 total CPU consuming queries
  17. SELECT TOP 10
  18. QT.TEXT AS STATEMENT_TEXT,
  19. QP.QUERY_PLAN,
  20. QS.TOTAL_WORKER_TIME AS CPU_TIME
  21. FROM SYS.DM_EXEC_QUERY_STATS QS
  22. CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
  23. CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
  24. ORDER BY TOTAL_WORKER_TIME DESC
  25.  
  26. --Top 10 average CPU consuming queries
  27. SELECT TOP 10
  28. TOTAL_WORKER_TIME ,
  29. EXECUTION_COUNT ,
  30. TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,
  31. QT.TEXT AS QUERYTEXT
  32. FROM SYS.DM_EXEC_QUERY_STATS QS
  33. CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT
  34. ORDER BY QS.TOTAL_WORKER_TIME DESC ;
  35.  
  36. --Top 10 I/O intensive queries
  37. SELECT TOP 10
  38. TOTAL_LOGICAL_READS,
  39. TOTAL_LOGICAL_WRITES,
  40. EXECUTION_COUNT,
  41. TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],
  42. QT.TEXT AS QUERY_TEXT,
  43. DB_NAME(QT.DBID) AS DATABASE_NAME,
  44. QT.OBJECTID AS OBJECT_ID
  45. FROM SYS.DM_EXEC_QUERY_STATS QS
  46. CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT
  47. WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0
  48. ORDER BY [IO_TOTAL] DESC
  49.  
  50. --Execution count of each query
  51. SELECT QS.EXECUTION_COUNT,
  52. QT.TEXT AS QUERY_TEXT,
  53. QT.DBID,
  54. DBNAME= DB_NAME (QT.DBID),
  55. QT.OBJECTID,
  56. QS.TOTAL_ROWS,
  57. QS.LAST_ROWS,
  58. QS.MIN_ROWS,
  59. QS.MAX_ROWS
  60. FROM SYS.DM_EXEC_QUERY_STATS AS QS
  61. CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
  62. ORDER BY QS.EXECUTION_COUNT DESC