مانیتورینگ SQL Server شناسایی دستورات SQL در حال اجرا با DMV

مانیتورینگ لحظه‌ای: چگونه دستورات SQL در حال اجرا را شناسایی کنیم؟

شناسایی دستورات SQL در حال اجرا در SQL Server برای عیب‌یابی عملکرد و پایش سلامت سرور بسیار حیاتی است. این مقاله به شما نشان می‌دهد چگونه با استفاده از Dynamic Management Views (DMVs)، دستورات در حال اجرا را به سرعت و دقت مشاهده کنید. درک فعالیت‌های جاری پایگاه داده به شما امکان می‌دهد تا گلوگاه‌ها را شناسایی کرده و بهینه‌سازی‌های لازم را انجام دهید.

ساده‌ترین روش برای مشاهده درخواست‌های در حال اجرا، پرس‌وجو از DMV sys.dm_exec_requests است. این DMV اطلاعات کلیدی درباره هر درخواست که در SQL Server در حال اجراست، فراهم می‌کند.

SELECT session_id, command, status, blocking_session_id, wait_type, wait_time, start_time
FROM sys.dm_exec_requests
WHERE status = 'running'

این پرس‌وجو درخواست‌های فعال را نمایش می‌دهد، شامل session_id (شناسه جلسه)، command (نوع دستور)، status (وضعیت فعلی)، blocking_session_id (شناسه جلسه‌ای که باعث انسداد شده)، wait_type (نوع انتظار)، wait_time (مدت زمان انتظار) و start_time (زمان شروع).

در حالی که sys.dm_exec_requests اطلاعات سطح بالا را ارائه می‌دهد، اغلب به متن کامل SQL نیاز داریم. می‌توانید با جوین کردن (JOIN) این DMV با sys.dm_exec_sql_text با استفاده از sql_handle، متن کامل دستور را بازیابی کنید.

SELECT
    r.session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    SUBSTRING(st.text, (r.statement_start_offset / 2) + 1,
              ((CASE r.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2) + 1
             ) AS statement_text,
    st.text AS full_command_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.status = 'running'
  AND r.session_id  @@SPID

این پرس‌وجوی پیشرفته‌تر از CROSS APPLY برای پیوند دادن درخواست‌ها به متن SQL مربوطه‌شان استفاده می‌کند. منطق SUBSTRING، دستور خاص در حال اجرا را از بچ (batch) استخراج می‌کند، در حالی که full_command_text کل بچ را نشان می‌دهد. فیلتر کردن با session_id @@SPID، پرس‌وجوی فعلی شما را از نتایج حذف می‌کند.

برای دریافت اطلاعات زمینه‌ای بهتر، ممکن است بخواهید بدانید چه کسی پرس‌وجو را اجرا می‌کند یا از کجا. این کار با جوین کردن sys.dm_exec_requests با sys.dm_exec_sessions امکان‌پذیر است.

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    r.session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    SUBSTRING(st.text, (r.statement_start_offset / 2) + 1,
              ((CASE r.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2) + 1
             ) AS statement_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(r.sql_handle) AS st
WHERE r.status = 'running'
  AND r.session_id  @@SPID
  AND s.is_user_process = 1

با جوین کردن با sys.dm_exec_sessions، می‌توانیم login_name (نام کاربری)، host_name (نام میزبان) و program_name (نام برنامه) را بازیابی کنیم. فیلتر s.is_user_process = 1 کمک می‌کند تا بر فرآیندهای شروع شده توسط کاربر تمرکز کنید تا فرآیندهای سیستمی.

انسداد (Blocking) یک مشکل رایج در عملکرد SQL Server است. می‌توانید جلسات انسدادکننده را با بررسی ستون blocking_session_id شناسایی کنید.

SELECT
    r.session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    s.login_name AS blocking_login_name,
    s.host_name AS blocking_host_name,
    st.text AS blocking_text,
    r.wait_type,
    r.wait_time
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_exec_sessions s ON r.blocking_session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) st
WHERE r.blocking_session_id  0

این پرس‌وجو به شناسایی جلسات مسدود شده و، مهم‌تر از آن، کاری که جلسه مسدودکننده انجام می‌دهد (در صورت موجود بودن) کمک می‌کند. این پرس‌وجو sys.dm_exec_sessions و sys.dm_exec_sql_text را دوباره جوین می‌کند تا جزئیات جلسه مسدودکننده را به دست آورد.

گاهی اوقات شما فقط به فعالیت‌های یک پایگاه داده خاص اهمیت می‌دهید. می‌توانید با استفاده از database_id از sys.dm_exec_requests فیلتر کرده و سپس با sys.databases جوین کنید.

SELECT
    DB_NAME(r.database_id) AS DatabaseName,
    r.session_id,
    r.command,
    r.status,
    SUBSTRING(st.text, (r.statement_start_offset / 2) + 1,
              ((CASE r.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2) + 1
             ) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE DB_NAME(r.database_id) = 'YourDatabaseName'
  AND r.status = 'running'

این رویکرد به شما امکان می‌دهد تا منحصراً بر روی پرس‌وجوهایی تمرکز کنید که در برابر یک پایگاه داده خاص در حال اجرا هستند، که اغلب در محیط‌های چند پایگاه داده‌ای بسیار مفید است. ‘YourDatabaseName’ را با نام واقعی پایگاه داده مورد نظر خود جایگزین کنید.

پایش مؤثر دستورات SQL در حال اجرا یک مهارت بنیادی برای هر مدیر SQL Server یا توسعه‌دهنده است. با استفاده از DMVهایی مانند sys.dm_exec_requests، sys.dm_exec_sql_text و sys.dm_exec_sessions، بینش عمیقی نسبت به فعالیت‌های سرور خود به دست می‌آورید، که امکان تنظیم عملکرد پیشگیرانه و عیب‌یابی سریع را فراهم می‌کند.