Realizar consulta que liste os procedimentos mais lentos(demorados)

Realizar consulta que liste os procedimentos mais lentos(demorados) que ocorrem dentro do banco de dados SQL Server

SELECT TOP 20
GETDATE() AS ‘Collection Date’,
qs.execution_count AS ‘Execution Count’,
SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2
) AS ‘Query Text’,
DB_NAME(qt.dbid) AS ‘DB Name’,
qs.total_worker_time AS ‘Total CPU Time’, qs.total_worker_time/qs.execution_count AS ‘Avg CPU Time (ms)’, qs.total_physical_reads AS ‘Total Physical Reads’, qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’, qs.total_logical_reads AS ‘Total Logical Reads’, qs.total_logical_reads/qs.execution_count AS ‘Avg Logical Reads’, qs.total_logical_writes AS ‘Total Logical Writes’, qs.total_logical_writes/qs.execution_count AS ‘Avg Logical Writes’, qs.total_elapsed_time AS ‘Total Duration’, qs.total_elapsed_time/qs.execution_count AS ‘Avg Duration (ms)’, qp.query_plan AS ‘Plan’
FROM sys.dm_exec_query_stats AS 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 WHERE qs.execution_count > 50 OR qs.total_worker_time/qs.execution_count > 100 OR qs.total_physical_reads/qs.execution_count > 1000 OR qs.total_logical_reads/qs.execution_count > 1000 OR qs.total_logical_writes/qs.execution_count > 1000 OR qs.total_elapsed_time/qs.execution_count > 1000 ORDER BY qs.execution_count DESC, qs.total_elapsed_time/qs.execution_count DESC, qs.total_worker_time/qs.execution_count DESC, qs.total_physical_reads/qs.execution_count DESC, qs.total_logical_reads/qs.execution_count DESC, qs.total_logical_writes/qs.execution_count DESC GO

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>