Top 10 queries by CPU logical reads

Configurare noua (How To)

Situatie

Mai jos este un select care returneaza top 10 queriuri cele mai consumatoare pe counterul CPU – Logical reads. Este folositor atunci cand sunt probleme de performanta pe un server MSSQL Server.

Solutie

SELECT

TOP 10
qs.total_elapsed_time / qs.execution_count AS [AvgExecTime],
qs.execution_count,
qs.total_logical_reads,
qs.total_worker_time / qs.execution_count AS [AvgCPUTime],
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) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY [AvgExecTime] DESC

Tip solutie

Permanent
Etichetare:

Voteaza

Up Down
(1 din 1 persoane apreciaza acest articol)
Share
Tweet
Share

Despre Autor

Leave A Comment?