Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- sys.dm_exec_query_stats
- 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.
- Remarks
- Statistics in the view are updated when a query is completed.
- User required VIEW SERVER STATE permission on the server.
- Reference
- http://msdn.microsoft.com/en-us/library/ms189741.aspx
- http://mssqlfun.com
- */
- --Top 10 total CPU consuming queries
- SELECT TOP 10
- QT.TEXT AS STATEMENT_TEXT,
- QP.QUERY_PLAN,
- QS.TOTAL_WORKER_TIME AS CPU_TIME
- FROM SYS.DM_EXEC_QUERY_STATS QS
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
- CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
- ORDER BY TOTAL_WORKER_TIME DESC
- --Top 10 average CPU consuming queries
- SELECT TOP 10
- TOTAL_WORKER_TIME ,
- EXECUTION_COUNT ,
- TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,
- QT.TEXT AS QUERYTEXT
- FROM SYS.DM_EXEC_QUERY_STATS QS
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT
- ORDER BY QS.TOTAL_WORKER_TIME DESC ;
- --Top 10 I/O intensive queries
- SELECT TOP 10
- TOTAL_LOGICAL_READS,
- TOTAL_LOGICAL_WRITES,
- EXECUTION_COUNT,
- TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],
- QT.TEXT AS QUERY_TEXT,
- DB_NAME(QT.DBID) AS DATABASE_NAME,
- QT.OBJECTID AS OBJECT_ID
- FROM SYS.DM_EXEC_QUERY_STATS QS
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT
- WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0
- ORDER BY [IO_TOTAL] DESC
- --Execution count of each query
- SELECT QS.EXECUTION_COUNT,
- QT.TEXT AS QUERY_TEXT,
- QT.DBID,
- DBNAME= DB_NAME (QT.DBID),
- QT.OBJECTID,
- QS.TOTAL_ROWS,
- QS.LAST_ROWS,
- QS.MIN_ROWS,
- QS.MAX_ROWS
- FROM SYS.DM_EXEC_QUERY_STATS AS QS
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT
- ORDER BY QS.EXECUTION_COUNT DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement