Narcis Miclaus

6 min di lettura

SQL Server, dalle fondamenta Lezione 36 / 40

DMV che ogni DBA dovrebbe sapere a memoria

Le otto query che esegui su un server freddo. Wait stats, blocking, top CPU, top I/O, buffer pool, indici mancanti, indici inutilizzati, panoramica del sistema.

Qualcuno ti consegna un SQL Server. “Scopri cosa c’è che non va.” Niente contesto. Niente accesso al monitoraggio. Niente storia di deploy.

Cosa esegui?

Questa lezione è le otto query che ogni DBA dovrebbe avere memorizzate (o per lo meno in una cassetta degli attrezzi) per valutare un server sconosciuto in meno di dieci minuti. Le Dynamic Management View — DMV — sono la telemetria integrata di SQL Server. Otto query ben scelte ti dicono più di un giorno di lettura.

Ogni query di questa lezione funziona su SQL Server 2019+ e Azure SQL MI. Azure SQL DB ne omette alcune (DBCC, DMV cross-database); segnalo dove serve.

1. La query “cosa sto guardando”

SELECT
    @@SERVERNAME                   AS server_name,
    @@VERSION                       AS version_string,
    SERVERPROPERTY('Edition')       AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel')  AS product_level,
    SERVERPROPERTY('MachineName')   AS machine_name,
    SERVERPROPERTY('InstanceDefaultDataPath') AS default_data,
    SERVERPROPERTY('InstanceDefaultLogPath')  AS default_log,
    -- Memoria
    (SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)') AS max_mem_mb,
    -- CPU / OS
    cpu_count, hyperthread_ratio, physical_memory_kb / 1024 AS physical_memory_mb,
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Prima cosa che fai. “Quale versione, quale edizione, quanta RAM, quanti core, da quanto è acceso?”

Se il server è partito 12 minuti fa, tutte le tue wait stat sono inutili (si resettano al riavvio).

2. Wait stat

WITH Waits AS (
    SELECT
        wait_type,
        wait_time_ms / 1000.0                                   AS wait_s,
        (wait_time_ms - signal_wait_time_ms) / 1000.0           AS resource_s,
        signal_wait_time_ms / 1000.0                             AS signal_s,
        waiting_tasks_count                                      AS wait_count,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ()        AS pct,
        ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC)           AS rn
    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', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'DIRTY_PAGE_POLL',
        'BROKER_PARTITION_COLLECT', 'WAIT_FOR_RESULTS'
    )
)
SELECT TOP (15)
    wait_type, wait_s, resource_s, signal_s, wait_count, CAST(pct AS DECIMAL(5,2)) AS pct
FROM Waits
ORDER BY wait_time_ms DESC;

La lista di esclusione filtra i tipi di attesa “di background” che non contano. Il resto ti dice su cosa il server sta effettivamente spendendo tempo.

Schemi comuni:

  • PAGEIOLATCH_* — in attesa sul disco. I/O lento.
  • LCK_* — attese di lock. Blocking.
  • ASYNC_NETWORK_IO — in attesa che il client consumi i risultati. Result set grossi, applicazione lenta.
  • CXPACKET, CXCONSUMER — parallelismo. Di solito va bene; indaga se è estremo.
  • SOS_SCHEDULER_YIELD — pressione sulla CPU.
  • PAGELATCH_* — contesa di tempdb o pagine calde.

Se conosci le tue attese, sei al 50% del cammino per diagnosticare qualsiasi problema di performance.

3. Chi sta girando proprio adesso

Dalla lezione 20, adattata:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time / 1000.0   AS cpu_s,
    r.reads, r.writes, r.logical_reads,
    SUBSTRING(t.text,
              r.statement_start_offset / 2 + 1,
              (CASE r.statement_end_offset
                   WHEN -1 THEN DATALENGTH(t.text)
                   ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2 + 1) AS current_statement
FROM sys.dm_exec_sessions  AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
ORDER BY r.cpu_time DESC;

La terza cosa che esegui dopo le wait stat. “Cosa sta facendo questo server proprio adesso?” Ordinato per CPU: la query attiva più pesante è in cima.

4. Top query per risorsa

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000 / NULLIF(qs.execution_count, 0) AS avg_cpu_ms,
    qs.total_elapsed_time / 1000 / NULLIF(qs.execution_count, 0) AS avg_elapsed_ms,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0)        AS avg_logical_reads,
    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_text,
    qp.query_plan
FROM sys.dm_exec_query_stats              AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)     AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_cpu_ms DESC;

Le top 20 query per CPU media. Sostituisci avg_cpu_ms con avg_logical_reads o execution_count per viste diverse.

Se questo server ha Query Store attivo (lezione 28), preferiscilo per le domande storiche. Questa DMV conosce solo i piani attualmente nella plan cache.

5. Buffer pool: cosa c’è in memoria

SELECT TOP (20)
    DB_NAME(bp.database_id)        AS database_name,
    COUNT(*) * 8 / 1024            AS mb_in_cache,
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors) AS pct_of_buffer_pool
FROM sys.dm_os_buffer_descriptors AS bp
GROUP BY DB_NAME(bp.database_id)
ORDER BY mb_in_cache DESC;

Quali database posseggono quanto del buffer pool. Ti dice cosa è caldo.

Per un singolo database, quali tabelle:

USE Runehold;
GO

SELECT TOP (20)
    OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS obj,
    COUNT(*) * 8 / 1024 AS mb_in_cache
FROM sys.dm_os_buffer_descriptors AS bp
JOIN sys.allocation_units         AS au ON au.allocation_unit_id = bp.allocation_unit_id
JOIN sys.partitions               AS p  ON p.partition_id = au.container_id OR (au.type = 2 AND au.container_id = p.hobt_id)
WHERE bp.database_id = DB_ID()
GROUP BY OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY mb_in_cache DESC;

Ordina le tabelle grandi per impronta in cache. Le tabelle calde in cima. Se una tabella “piccola” è in cima, c’è qualcosa che non va (troppe scan?).

6. Indici mancanti

Dalla lezione 22:

SELECT TOP (20)
    [Impact] = mig_stats.avg_total_user_cost * (mig_stats.avg_user_impact / 100.0) * (mig_stats.user_seeks + mig_stats.user_scans),
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mig_stats.user_seeks + mig_stats.user_scans AS seeks_scans
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats  AS mig_stats ON mig_stats.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details      AS mid       ON mid.index_handle    = mig.index_handle
ORDER BY [Impact] DESC;

Top 20 suggerimenti. Valuta, non creare alla cieca.

7. Indici inutilizzati

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS reads,
    ius.user_updates          AS writes,
    i.is_disabled
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON ius.object_id   = i.object_id
   AND ius.index_id    = i.index_id
   AND ius.database_id = DB_ID()
WHERE i.object_id > 100
  AND i.type_desc = 'NONCLUSTERED'
ORDER BY (ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0)) ASC;

Indici con pochissime letture ma scritture reali. Candidati al drop.

Avvertenza: dm_db_index_usage_stats si resetta al riavvio del server. Se il server è stato riavviato ieri, questi numeri non significano nulla. Verifica che il server sia attivo da almeno una settimana.

8. Uso dei file dei database

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.name                 AS logical_name,
    mf.type_desc,
    mf.size * 8 / 1024      AS size_mb,
    CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT) * 8 / 1024 AS used_mb,
    mf.growth, mf.is_percent_growth
FROM sys.master_files AS mf
WHERE mf.database_id > 4  -- salta i database di sistema
ORDER BY size_mb DESC;

Ti dà un dettaglio dei file per database. Utile quando il disco si sta riempiendo e devi sapere quale database è responsabile.

Bonus: la query “deadlock graph dalla sessione system_health”

Dalla lezione 20:

SELECT TOP (10)
    XEvent.value('(@timestamp)[1]', 'DATETIME2') AS ts,
    XEvent.query('.')                              AS deadlock_xml
FROM (
    SELECT CAST(target_data AS XML) AS td
    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 source
CROSS APPLY td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS X(XEvent)
ORDER BY ts DESC;

Deadlock recenti catturati dalla sessione XE system_health di default. Essenziale quando qualcuno chiede “abbiamo avuto deadlock stanotte?”

Dove tenere queste

Ogni DBA ha una cartella “SQL toolbox” sul portatile — una raccolta di file .sql con query come queste, nominati chiaramente (00-system-overview.sql, 01-wait-stats.sql, eccetera). Copia questa lezione nella tua.

Per versioni ancora più rifinite di queste, vedi le lezioni 37-40 sul First Responder Kit di Brent Ozar. Le versioni “pro” producono risultati formattati con codice colore, prioritizzazione e link a post di blog che spiegano ogni problema.

Prova questo sulla tua macchina

Raggruppa le otto query qui sopra in un unico file .sql. Salvalo come dba-overview.sql. Ogni volta che metti mano a un SQL Server sconosciuto, eseguilo dall’alto verso il basso. Dieci minuti di output ti diranno il 90% di quello che ti serve sapere per iniziare a diagnosticare.

Modulo 7 chiuso — DBA essentials coperti dall’inizio alla fine. Prossimo: l’Emergency Kit. Le lezioni 37-40 coprono il First Responder Kit di Brent Ozar (sp_Blitz, sp_BlitzIndex, sp_BlitzCache) e sp_WhoIsActive di Adam Machanic. Gli strumenti che ogni DBA serio tiene affilati.

Cerca