SQL Stats to help identify bottlenecks.

Query 1 : 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

 

Query 2 : 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 ;

 

Query 3 : 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 

 

Query 4 : 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