Indicadores de monitoramento SQL Server

 Indicadores essenciais para monitoramento

▶ Buffer Cache Hit Ratio

Mede a eficiência da leitura de páginas na memória sem precisar acessar o disco.

Percentual de páginas lidas da memória versus disco

Alta taxa é ideal (> 95%).

SELECT 

  (a.cntr_value  1.0 / b.cntr_value)  100.0 AS BufferCacheHitRatio

FROM 

  sys.dm_os_performance_counters a

JOIN 

  sys.dm_os_performance_counters b

  ON a.object_name = b.object_name

WHERE 

  a.counter_name = 'Buffer cache hit ratio'

  AND b.counter_name = 'Buffer cache hit ratio base'

  AND a.object_name LIKE '%Buffer Manager%';

▶ Page Life Expectancy (PLE)

Indica quanto tempo, em segundos, uma página permanece no cache.

Valores baixos (< 300) podem indicar pressão de memória.

(Acima de 300 segundos por 4GB de RAM.)

SELECT 

  [Page Life Expectancy] = cntr_value

FROM 

  sys.dm_os_performance_counters

WHERE 

  counter_name = 'Page life expectancy'

  AND object_name LIKE '%Buffer Manager%';

▶ Batch Requests/sec

Mostra a quantidade de requisições enviadas ao SQL Server por segundo.

Útil para analisar carga geral.

SELECT 

  [Batch Requests/sec] = cntr_value

FROM 

  sys.dm_os_performance_counters

WHERE 

  counter_name = 'Batch Requests/sec'

  AND object_name LIKE '%SQL Statistics%';

▶  Wait Stats

Revelam onde o SQL Server está esperando mais — seja por CPU, disco, bloqueios, etc. Ajuda a identificar gargalos. Tempo total que o SQL Server esperou por recursos.

  SELECT 

  wait_type,

  wait_time_ms / 1000.0 AS wait_time_sec,

  waiting_tasks_count

FROM 

  sys.dm_os_wait_stats

WHERE 

  wait_type NOT IN (

    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',

    'SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',

    'XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',

    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN',

    'WAIT_XACT_OWN_TRANSACTION'

  )

ORDER BY wait_time_ms DESC;

▶  Fragmentação de índices

Fragmentos excessivos reduzem a eficiência das consultas. Acompanhe e reorganize ou reconstrua índices periodicamente.

SELECT 

  dbschemas.[name] AS 'Schema',

  dbtables.[name] AS 'Table',

  dbindexes.[name] AS 'Index',

  indexstats.avg_fragmentation_in_percent

FROM 

  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS indexstats

INNER JOIN 

  sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN 

  sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN 

  sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

  AND indexstats.index_id = dbindexes.index_id

WHERE 

  indexstats.database_id = DB_ID()

  AND indexstats.avg_fragmentation_in_percent > 10

ORDER BY 

  indexstats.avg_fragmentation_in_percent DESC;

▶  Deadlocks

Indicam conflitos de concorrência entre transações. Devem ser analisados e evitados com estratégias de controle de concorrência.

SELECT 

  deadlock_graph = XEventData.value('(event/data[@name="xml_report"]/value)[1]', 'XML'),

  timestamp = XEventData.value('(event/@timestamp)[1]', 'datetime')

FROM (

    SELECT 

      CAST(target_data AS XML) AS TargetData

    FROM 

      sys.dm_xe_session_targets st

    JOIN 

      sys.dm_xe_sessions s ON s.address = st.event_session_address

    WHERE 

      s.name = 'system_health'

      AND st.target_name = 'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEvent(XEventData);

▶ Planos de execução ruins

Verificar planos ineficientes no cache pode ajudar a otimizar queries lentas.

SELECT TOP 10

    qs.execution_count,

    qs.total_worker_time AS TotalCPU,

    qs.total_elapsed_time / qs.execution_count AS AvgElapsed,

    SUBSTRING(qt.text, qs.statement_start_offset/2, 

        (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 QueryText

FROM 

    sys.dm_exec_query_stats qs

CROSS APPLY 

    sys.dm_exec_sql_text(qs.sql_handle) AS qt

ORDER BY 

    qs.total_worker_time DESC;

▶ Uso de CPU, Memória e Disco

Monitoramento contínuo do sistema operacional ajuda a identificar sobrecarga de recursos físicos.

    SELECT 

    r.session_id,

    s.login_name,

    r.status,

    r.cpu_time,

    r.total_elapsed_time,

    SUBSTRING(st.text, r.statement_start_offset/2, 

              (CASE WHEN r.statement_end_offset = -1

              THEN LEN(CONVERT(nvarchar(max), st.text)) * 2

              ELSE r.statement_end_offset END - r.statement_start_offset)/2) AS query_text

FROM 

    sys.dm_exec_requests r

JOIN 

    sys.dm_exec_sessions s ON r.session_id = s.session_id

CROSS APPLY 

    sys.dm_exec_sql_text(sql_handle) AS st

ORDER BY r.cpu_time DESC;

Painel de Monitoramento de Desempenho do SQL Server

-- Painel de Monitoramento de Desempenho do SQL Server

-- 1. Buffer Cache Hit Ratio

SELECT 

  'Buffer Cache Hit Ratio' AS Indicador,

  (a.cntr_value  1.0 / NULLIF(b.cntr_value, 0))  100.0 AS Valor

FROM 

  sys.dm_os_performance_counters a

JOIN 

  sys.dm_os_performance_counters b

    ON a.object_name = b.object_name

WHERE 

  a.counter_name = 'Buffer cache hit ratio'

  AND b.counter_name = 'Buffer cache hit ratio base'

  AND a.object_name LIKE '%Buffer Manager%';

-- 2. Page Life Expectancy

SELECT 

  'Page Life Expectancy' AS Indicador,

  cntr_value AS Valor

FROM 

  sys.dm_os_performance_counters

WHERE 

  counter_name = 'Page life expectancy'

  AND object_name LIKE '%Buffer Manager%';

-- 3. Batch Requests/sec

SELECT 

  'Batch Requests/sec' AS Indicador,

  cntr_value AS Valor

FROM 

  sys.dm_os_performance_counters

WHERE 

  counter_name = 'Batch Requests/sec'

  AND object_name LIKE '%SQL Statistics%';

-- 4. Top Wait Stats

SELECT TOP 10

  wait_type AS Indicador,

  wait_time_ms / 1000.0 AS Valor,

  waiting_tasks_count AS Contagem

FROM 

  sys.dm_os_wait_stats

WHERE 

  wait_type NOT IN (

    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',

    'SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',

    'XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',

    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN',

    'WAIT_XACT_OWN_TRANSACTION'

  )

ORDER BY wait_time_ms DESC;

-- 5. Fragmentação de Índices (>10%)

SELECT 

  dbschemas.name AS SchemaName,

  dbtables.name AS TableName,

  dbindexes.name AS IndexName,

  indexstats.avg_fragmentation_in_percent AS Fragmentacao

FROM 

  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS indexstats

JOIN 

  sys.tables dbtables ON dbtables.object_id = indexstats.object_id

JOIN 

  sys.schemas dbschemas ON dbtables.schema_id = dbschemas.schema_id

JOIN 

  sys.indexes dbindexes ON dbindexes.object_id = indexstats.object_id

  AND indexstats.index_id = dbindexes.index_id

WHERE 

  indexstats.database_id = DB_ID()

  AND indexstats.avg_fragmentation_in_percent > 10

ORDER BY indexstats.avg_fragmentation_in_percent DESC;

-- 6. Deadlocks recentes (XEvent System Health)

SELECT TOP 5

  XEventData.value('(event/@timestamp)[1]', 'datetime') AS DataHora,

  CAST(XEventData.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS XML) AS DeadlockXML

FROM (

    SELECT CAST(target_data AS XML) AS TargetData

    FROM sys.dm_xe_session_targets st

    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

    WHERE s.name = 'system_health'

      AND st.target_name = 'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEvent(XEventData);

-- 7. Consultas mais custosas (CPU)

SELECT TOP 5

    qs.execution_count,

    qs.total_worker_time AS TotalCPU,

    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS MediaTempo,

    SUBSTRING(qt.text, qs.statement_start_offset/2,

        (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 QueryText

FROM 

    sys.dm_exec_query_stats qs

CROSS APPLY 

    sys.dm_exec_sql_text(qs.sql_handle) AS qt

ORDER BY qs.total_worker_time DESC;

-- 8. Sessões com maior uso de CPU

SELECT TOP 5

    r.session_id,

    s.login_name,

    r.cpu_time,

    r.total_elapsed_time,

    SUBSTRING(st.text, r.statement_start_offset/2,

        (CASE 

            WHEN r.statement_end_offset = -1

                THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2

            ELSE r.statement_end_offset

         END - r.statement_start_offset)/2) AS QueryText

FROM 

    sys.dm_exec_requests r

JOIN 

    sys.dm_exec_sessions s ON r.session_id = s.session_id

CROSS APPLY 

    sys.dm_exec_sql_text(r.sql_handle) AS st

ORDER BY r.cpu_time DESC;