Narcis Miclaus

6 min de citit

SQL Server, de la zero Lecția 36 / 40

DMV-uri pe care fiecare DBA ar trebui să le știe pe de rost

Cele opt interogări pe care le rulezi pe un server rece. Wait stats, blocaje, top CPU, top I/O, buffer pool, indecși lipsă, indecși nefolosiți, prezentare generală a sistemului.

Cineva îți dă un SQL Server. „Dă-ți seama ce nu merge.” Fără context. Fără acces la monitorizare. Fără istoric de deployment.

Ce rulezi?

Lecția asta e setul de opt interogări pe care fiecare DBA ar trebui să le aibă memorate (sau cel puțin într-o trusă) pentru a evalua un server necunoscut în mai puțin de zece minute. Dynamic Management Views — DMV-uri — sunt telemetria încorporată a SQL Server. Opt interogări bine alese îți spun mai mult decât o zi de citit.

Fiecare interogare din lecția asta funcționează pe SQL Server 2019+ și Azure SQL MI. Azure SQL DB omite unele (DBCC, DMV-uri cross-database); voi nota unde.

1. Interogarea „la ce mă uit”

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,
    -- Memorie
    (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;

Primul lucru pe care îl faci. „Ce versiune, ce ediție, câtă RAM, câte nuclee, de cât timp e în funcțiune?”

Dacă serverul a pornit acum 12 minute, toate wait stats-urile sunt inutile (se resetează la repornire).

2. Wait stats

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;

Lista de excludere filtrează tipurile de așteptări „de fundal” care nu contează. Restul îți spune pe ce își petrece serverul timpul cu adevărat.

Tipare comune:

  • PAGEIOLATCH_* — așteaptă disc. I/O lent.
  • LCK_* — așteptări de lock-uri. Blocaje.
  • ASYNC_NETWORK_IO — așteaptă clientul să consume rezultatele. Seturi grase de rânduri, aplicație lentă.
  • CXPACKET, CXCONSUMER — paralelism. De obicei în regulă; investighează dacă e extrem.
  • SOS_SCHEDULER_YIELD — presiune pe CPU.
  • PAGELATCH_* — contenție pe tempdb sau pagini fierbinți.

Dacă îți cunoști waits-urile, ești la jumătatea drumului în diagnosticarea oricărei probleme de performanță.

3. Cine rulează chiar acum

Din lecția 20, adaptat:

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;

Al treilea lucru pe care îl rulezi după wait stats. „Ce face acest server chiar acum?” Sortat după CPU: query-ul activ cel mai greu e primul.

4. Top interogări după resurse

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;

Top 20 interogări după CPU mediu. Înlocuiește avg_cpu_ms cu avg_logical_reads sau execution_count pentru viziuni diferite.

Dacă acest server are Query Store activat (lecția 28), preferă-l pentru întrebări istorice. Acest DMV cunoaște doar planurile aflate în prezent în plan cache.

5. Buffer pool: ce e în memorie

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;

Care baze de date dețin câtă parte din buffer pool. Îți spune ce e fierbinte.

Pentru o singură bază de date, ce tabele:

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;

Sortează tabelele mari după amprenta în cache. Tabelele fierbinți sus. Dacă un tabel „mic” e sus, ceva nu e în regulă (prea multe scanări?).

6. Indecși lipsă

Din lecția 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 sugestii. Evaluează, nu crea orbește.

7. Indecși nefolosiți

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;

Indecși cu foarte puține citiri, dar cu scrieri reale. Candidați la ștergere.

Atenție: dm_db_index_usage_stats se resetează la repornirea serverului. Dacă serverul a repornit ieri, numerele astea nu înseamnă nimic. Verifică să fi fost up de cel puțin o săptămână.

8. Utilizarea fișierelor de bază de date

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  -- sare peste sistem
ORDER BY size_mb DESC;

Îți dă o defalcare a fișierelor per bază de date. Util când discul se umple și trebuie să știi care bază de date e responsabilă.

Bonus: interogarea „graficul deadlock din sesiunea system health”

Din lecția 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-uri recente capturate de sesiunea XE implicită system_health. Esențial când cineva întreabă „am avut deadlock-uri peste noapte?”

Unde să le ții

Fiecare DBA are un folder „SQL toolbox” pe laptop — o colecție de fișiere .sql cu interogări ca acestea, denumite clar (00-system-overview.sql, 01-wait-stats.sql etc.). Copiază lecția asta în trusa ta.

Pentru versiuni și mai șlefuite ale acestora, vezi lecțiile 37–40 despre First Responder Kit de la Brent Ozar. Versiunile pro afișează rezultate formatate, cu coduri de culoare, prioritizare și linkuri către articole de blog care explică fiecare problemă.

Rulează asta pe propria mașină

Adună cele opt interogări de mai sus într-un singur fișier .sql. Salvează-l ca dba-overview.sql. De fiecare dată când atingi un SQL Server necunoscut, rulează-l de sus până jos. Zece minute de output îți vor spune 90% din ce trebuie să știi pentru a începe diagnosticul.

Modulul 7 încheiat — esențiale DBA acoperite cap-coadă. Următoarele: trusa de urgență. Lecțiile 37–40 acoperă First Responder Kit-ul lui Brent Ozar (sp_Blitz, sp_BlitzIndex, sp_BlitzCache) și sp_WhoIsActive al lui Adam Machanic. Uneltele pe care orice DBA serios le ține ascuțite.

Caută