Suporte a DMVs

Cenários

• Ocorre um gargalo de desempenho e a eficiência de execução do banco de dados se torna baixa.
• O resultado do monitoramento mostra que a CPU e a I/O estão altas em alguns segmentos de tempo.

Procedimento

1. Use a conta rdsuser para se conectar à instância de BD de destino por meio de um cliente e execute as seguintes instruções no plano de gerenciamento:

   declare @DatabaseName nvarchar(100)
   set @DatabaseName = 'Wisdom_TT_ODS'
   
   select top 100
   DB_NAME(st.dbid) as DBName, OBJECT_NAME(st.objectid,st.dbid) as ObjectName,
   substring(st.text,(qs.statement_start_offset/2)+1,((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as Statement,
   st.text as Query,
   qp.query_plan,
   plan_generation_num,
   creation_time,
   last_execution_time,
   execution_count,
   total_worker_time,
   min_worker_time,
   max_worker_time,
   total_logical_reads,
   min_logical_reads,
   max_logical_reads,
   total_elapsed_time,
   min_elapsed_time,
   max_elapsed_time,
   total_rows,
   min_rows,
   max_rows,
   ,total_worker_time/execution_count as avg_worker_time --- Average CPU duration
   ,total_logical_reads/execution_count as avg_logical_reads --- Average logical reads
   ,total_elapsed_time/execution_count as avg_elapsed_time --- Average total duration
   ,total_rows/execution_count as avg_rows --- Average data processing rows
   sql_handle,
   plan_handle,
   query_hash,
   query_plan_hash
   from sys.dm_exec_query_stats qs
   cross apply sys.dm_exec_sql_text(plan_handle) st
   cross apply sys.dm_exec_query_plan(plan_handle) qp
   where st.dbid=DB_ID(@DatabaseName)
   and text not like '%sys.%'and text not like '%[[]sys]%'
   order by avg_worker_time desc

2. Você pode exibir os registros de execução SQL e os detalhes de consumo de recursos do banco de dados correspondente no resultado da consulta.