8/30/2015 11:01:54 PM
Viewed: 732
tamkhong
2/5/2013 4:59:47 PM
Get High Quality Information About Query Performance

How to Find the Top Most Expensive Cached Queries: sys.dm_exec_query_stats

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
Query 5: 
SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
Query 6: 
SELECT 

(total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]

, max_elapsed_time/1000 AS [MaxExecTime in ms]

, min_elapsed_time/1000 AS [MinExecTime in ms]

, (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]

, qs.execution_count AS NumberOfExecs

, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]

, max_logical_reads AS MaxLogicalReads

, min_logical_reads AS MinLogicalReads

, max_logical_writes AS MaxLogicalWrites

, min_logical_writes AS MinLogicalWrites

,(

SELECT SUBSTRING(text,statement_start_offset/2,

(

CASE WHEN statement_end_offset = -1

then LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offset

end -statement_start_offset)/2

)

FROM sys.dm_exec_sql_text(sql_handle)

) AS query_text

FROM sys.dm_exec_query_stats qs

ORDER BY [Avg Exec Time in ms] DESC


Gửi trả lời