SQL SERVER – Expensive Queries
Para analisar a performance de uma base de dados esta script é um bom ponto de partida.
Avalie tanbém a necessidade de existirem índices em falta no artigo “Missing Index Script”.
SELECT TOP 25
QuerySQL = SUBSTRING(
QT.TEXT
,( QS.statement_start_offset / 2) + 1
, (
(CASE WHEN QS.statement_end_offset = -1
THEN DATALENGTH(QT.TEXT)
ELSE QS.statement_end_offset
END
- QS.statement_start_offset
) / 2
)
+ 1
)
, ExecutionCount = QS.execution_count
, TotalReads = QS.total_logical_reads
, TotalWrites = QS.total_logical_writes
, TotalWork = QS.total_worker_time
, LastReads = QS.last_logical_reads
, LastWrites = QS.last_logical_writes
, LastWork = QS.last_worker_time
, LastExecutionTime = QS.last_execution_time
, QueryPlan = 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