ניטור MS SQL Server: DMV, Extended Events ו-Query Store

אתה מריץ דוח בשעה 14:32, והוא נתקע למשך 3 דקות. תוכנית השאילתה השתנתה, הסטטיסטיקות מיושנות—אין רישום שגיאות או מוני תקן שמראים את הסיבה. ללא ניטור מוגדר כראוי, אתה נשאר בחושך. כל שעת השבתה של יישום יכולה לעלות עשרות אלפי דולרים. עם הגישה שלנו—שאילתה ספציפית, תוכנית, נעילה. במאמר זה: ערכת הכלים ומתודולוגיית ההתקנה.

אילו בעיות אנחנו פותרים?

חסימות, deadlocks, ירידה בביצועי שאילתות, מחסור ב-CPU או I/O, תוכניות לא אופטימליות—כל אלה מאובחנים באמצעות DMVs, Extended Events ו-Query Store. ניטור פרואקטיבי מאפשר לזהות בעיות לפני שהן משפיעות על המשתמשים. מקרים אמיתיים: לקוח אחד לאחר ההתקנה הפחית את זמן התגובה הממוצע ב-40%, אחר צמצם את מספר ה-deadlocks מ-50 ל-2 ביום.

איך אנחנו עושים את זה

מהם DMVs ואיך הם עוזרים?

Dynamic Management Views הם תצוגות מובנות לאבחון בזמן אמת. הם לא דורשים התקנת סוכן. אנחנו משתמשים בהם כל הזמן כדי למצוא שאילתות כבדות וצווארי בקבוק. לצורך התקנה, אנחנו משתמשים ב-SSMS או בסקריפטים.

-- Текущие активные запросы с временем выполнения и wait types
SELECT r.session_id, r.status, r.wait_type, r.wait_time / 1000.0 AS wait_seconds,
       r.cpu_time / 1000.0 AS cpu_seconds,
       r.total_elapsed_time / 1000.0 AS elapsed_seconds,
       r.logical_reads,
       DB_NAME(r.database_id) AS database_name,
       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 query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

-- Топ-10 самых дорогих запросов по CPU за всё время
SELECT TOP 10
       qs.total_worker_time / qs.execution_count AS avg_cpu_us,
       qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us,
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
                 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
                   ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_us DESC;

-- Накопленные wait statistics с момента запуска SQL Server
SELECT TOP 15
       wait_type,
       waiting_tasks_count,
       wait_time_ms / 1000.0 AS wait_time_sec,
       max_wait_time_ms / 1000.0 AS max_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_wait_sec,
       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

סטטיסטיקות המתנה — כלי האבחון הראשון. -- Текущие активные запросы с временем выполнения и wait types SELECT r.session_id, r.status, r.wait_type, r.wait_time / 1000.0 AS wait_seconds, r.cpu_time / 1000.0 AS cpu_seconds, r.total_elapsed_time / 1000.0 AS elapsed_seconds, r.logical_reads, DB_NAME(r.database_id) AS database_name, 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 query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50 ORDER BY r.total_elapsed_time DESC; -- Топ-10 самых дорогих запросов по CPU за всё время SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu_us, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us, qs.execution_count, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY avg_cpu_us DESC; -- Накопленные wait statistics с момента запуска SQL Server SELECT TOP 15 wait_type, waiting_tasks_count, wait_time_ms / 1000.0 AS wait_time_sec, max_wait_time_ms / 1000.0 AS max_wait_sec, signal_wait_time_ms / 1000.0 AS signal_wait_sec, CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE', 'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH', 'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT' ) ORDER BY wait_time_ms DESC; — SQL Server ממתין לקריאות דיסק, צוואר בקבוק של I/O. PAGEIOLATCH_SH/EX — נעילות, בדוק את LCK_M_* עבור blocking_session_id. sys.dm_exec_requests — שאילתות מקבילות, יכולות להיות תקינות או סימן לתוכניות גרועות. הטבלה שלהלן מציגה סוגי המתנה אופייניים ופירושם:

למה Extended Events עדיפים על Profiler

SQL Profiler מיושן: הוא גורם לנעילות כבדות ולצריכת משאבים גבוהה. Extended Events הוא התחליף המודרני עם תקורה מתחת ל-2%. תשעים אחוז מהפונקציונליות של Profiler ב-2% תקורה. אנחנו מגדירים סשן ללכידת שאילתות איטיות (ארוכות מ-5 שניות) לקובץ עם רוטציה של 100 MB:

CREATE EVENT SESSION [SlowQueries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed ( 
    ACTION ( sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name, sqlserver.plan_handle ) 
    WHERE ( [duration] > 5000000 AND [sqlserver].[database_name] <> N'master' ) 
), 
ADD EVENT sqlserver.rpc_completed ( 
    ACTION (sqlserver.sql_text, sqlserver.database_name) 
    WHERE [duration] > 5000000 
) 
ADD TARGET package0.ring_buffer (SET max_memory = 51200), 
ADD TARGET package0.asynchronous_file_target ( 
    SET filename = N'D:\SQLTrace\SlowQueries.xel', max_file_size = 100, max_rollover_files = 5 
) 
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON ); 
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

עבור deadlocks, אנחנו משתמשים ב-CXPACKET המובנה—הוא כבר לוכד אותם אוטומטית. אנחנו מחלצים גרפים דרך CREATE EVENT SESSION [SlowQueries] ON SERVER ADD EVENT sqlserver.sql_statement_completed ( ACTION ( sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name, sqlserver.plan_handle ) WHERE ( [duration] > 5000000 AND [sqlserver].[database_name] <> N'master' ) ), ADD EVENT sqlserver.rpc_completed ( ACTION (sqlserver.sql_text, sqlserver.database_name) WHERE [duration] > 5000000 ) ADD TARGET package0.ring_buffer (SET max_memory = 51200), ADD TARGET package0.asynchronous_file_target ( SET filename = N'D:\SQLTrace\SlowQueries.xel', max_file_size = 100, max_rollover_files = 5 ) WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON ); ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START; .

איך להגדיר Query Store לשליטה בתוכניות

Query Store הוא מאגר מובנה של היסטוריית תוכניות ומדדים. הוא מופעל ברמת מסד הנתונים. ההתקנה ארכה 15 דקות עבור לקוח אחד, והביאה להפחתה של 40% בזמן התגובה הממוצע. פרמטרים מומלצים:

ALTER DATABASE AppDB SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200 );
-- Поиск запросов, у которых план изменился в худшую сторону (например, за последние 24 часа)
SELECT TOP 20 q.query_id, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.execution_count, rs.avg_logical_io_reads, p.plan_id
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval i ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE i.start_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
-- Принудительно зафиксировать хороший план
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

השוואת שיטות ניטור

אינטגרציה עם מערכות חיצוניות

אנחנו מייצאים נתוני DMV ל-Prometheus דרך sql_exporter או Telegraf. ב-Grafana, אנחנו בונים דשבורדים לסטטיסטיקות המתנה, QDS ופעילות. התראות על מצבים קריטיים: CPU > 90%, LCK_M_X המתנה > 30 שניות, מילוי לוג > 80%.

תהליך העבודה

1. אנליטיקה — הבנת הארכיטקטורה, זיהוי נקודות כאב.
2. עיצוב — בחירת סשנים של Extended Events, סקריפטים של DMV, פרמטרים של Query Store.
3. יישום — פריסת סקריפטים, הגדרת התראות דרך SQL Server Agent.
4. אינטגרציה — חיבור ל-Grafana/Prometheus (אופציונלי).
5. בדיקות — אימות דיוק ההתראות, מדידת תקורה.
6. תיעוד — מסירת runbook ודשבורדים.

מה כלול בתוצר הסופי

• סקריפטים של DMV לאבחון יומיומי.
• סשן Extended Events מוכן לשימוש לשאילתות איטיות ו-deadlocks.
• Query Store מופעל ומוגדר עם מדיניות שמירה.
• התראות על סוגי המתנה קריטיים ומילוי לוג.
• דשבורד Grafana (אם נבחרה אינטגרציה).
• הדרכת צוות: איך לקרוא סטטיסטיקות המתנה ולנתח Query Store.

לוח זמנים ועלות

פרויקט אופייני אורך 2 עד 4 ימי עסקים, תלוי במורכבות התשתית (clustering, Always On, מספר מסדי נתונים). העלות מחושבת באופן אישי לאחר ביקורת של המצב הנוכחי שלך. החיסכון מהפחתת השבתות ואופטימיזציה של שאילתות יכול להיות משמעותי.

המהנדסים שלנו מחזיקים בהסמכות Microsoft ויש להם ניסיון של למעלה מ-10 שנים בניהול SQL Server. יישמנו ניטור לעשרות פרויקטים, כולל מערכות עם אלפי טרנזקציות בשנייה. תקורה מובטחת לא עולה על 5%.

צור קשר כדי לדון בפרויקט שלך. הזמן ביקורת ניטור היום.