۷ اسکریپت SQL Server برای مانیتورینگ عملکرد و سلامت پایگاه داده

هفت اسکریپت ضروری برای مانیتورینگ کارآمد SQL Server

مدیریت و پایش مستمر عملکرد SQL Server برای حفظ سلامت پایگاه داده، جلوگیری از مشکلات احتمالی و تضمین پایداری سیستم حیاتی است. متخصصان پایگاه داده و ادمین‌های SQL Server برای شناسایی bottlenecks و بهینه‌سازی منابع، به مجموعه‌ای از ابزارها و اسکریپت‌ها نیاز دارند. در این مقاله جامع، هفت اسکریپت کلیدی و کارآمد SQL Server را معرفی می‌کنیم که برای مانیتورینگ مداوم و تشخیص سریع مسائل عملکردی طراحی شده‌اند. این اسکریپت‌ها به شما کمک می‌کنند تا به سرعت وضعیت CPU، حافظه، فضای دیسک، قفل‌ها (blocking)، fragmentation ایندکس و وضعیت بکاپ‌ها را بررسی کرده و محیط پایگاه داده خود را در بهترین حالت نگه دارید.

1. پایش مصرف CPU در SQL Server

مصرف بالای CPU اغلب نشانه‌ای از کوئری‌های ناکارآمد، فعالیت زیاد کاربران یا کمبود منابع است. این اسکریپت اطلاعات جامعی از مصرف CPU توسط SQL Server و سایر فرآیندها ارائه می‌دهد که برای عیب‌یابی اولیه بسیار مفید است.

برای مشاهده وضعیت مصرف CPU، می‌توانید از کوئری زیر استفاده کنید:

SELECT
    @@SERVERNAME AS ServerName,
    DATEADD(ms, -(SUM(ms_ticks - (CASE WHEN system_idle_ms IS NULL THEN 0 ELSE system_idle_ms END)))/1000, GETDATE()) AS EventTime,
    SUM(CASE WHEN system_idle_ms IS NULL THEN 0 ELSE system_idle_ms END) AS SystemIdleTimeMs,
    SUM(CASE WHEN idle_ms IS NULL THEN 0 ELSE idle_ms END) AS IdleTimeMs,
    SUM(CASE WHEN sqlclr_ms IS NULL THEN 0 ELSE sqlclr_ms END) AS SQLCLRMs,
    SUM(CASE WHEN sqlserver_start_time IS NULL THEN 0 ELSE sqlserver_start_time END) AS SQLServerStartTimeMs,
    SUM(CASE WHEN sqlserver_process_cpu_ms IS NULL THEN 0 ELSE sqlserver_process_cpu_ms END) AS SQLServerProcessCPUMs,
    SUM(ms_ticks) AS TotalClockTimeMs,
    100 - SUM(CASE WHEN system_idle_ms IS NULL THEN 0 ELSE system_idle_ms END) * 100.0 / SUM(ms_ticks) AS SystemCPUUsagePercent,
    SUM(CASE WHEN sqlserver_process_cpu_ms IS NULL THEN 0 ELSE sqlserver_process_cpu_ms END) * 100.0 / SUM(ms_ticks) AS SQLServerProcessCPUUsagePercent
FROM sys.dm_os_sys_info WITH (NOLOCK);

این کوئری اطلاعاتی در مورد زمان بیکاری سیستم و SQL Server، و همچنین درصد استفاده از CPU توسط کل سیستم و فرآیند SQL Server ارائه می‌دهد که برای تشخیص سریع مشکلات عملکرد CPU حیاتی است.

2. بررسی وضعیت حافظه SQL Server

کمبود حافظه می‌تواند به شدت بر عملکرد SQL Server تأثیر بگذارد و باعث فراخوانی مداوم صفحات از دیسک (paging) شود. این اسکریپت جزئیات مصرف حافظه را نمایش می‌دهد تا بتوانید تخصیص منابع را بهینه کنید.

برای بررسی دقیق‌تر مصرف حافظه، کوئری زیر را اجرا کنید:

SELECT
    @@SERVERNAME AS ServerName,
    total_physical_memory_kb / 1024 AS TotalPhysicalMemoryMB,
    available_physical_memory_kb / 1024 AS AvailablePhysicalMemoryMB,
    total_page_file_kb / 1024 AS TotalPageFileMB,
    available_page_file_kb / 1024 AS AvailablePageFileMB,
    system_memory_state_desc AS SystemMemoryState,
    large_page_allocations_kb / 1024 AS LargePageAllocationsMB,
    process_physical_memory_low AS ProcessPhysicalMemoryLow,
    process_virtual_memory_low AS ProcessVirtualMemoryLow
FROM sys.dm_os_sys_info WITH (NOLOCK);

SELECT
    type,
    SUM(pages_kb) AS TotalPagesKB
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY type
ORDER BY SUM(pages_kb) DESC;

این کوئری دو بخش دارد: بخش اول اطلاعات کلی حافظه فیزیکی و مجازی سیستم را نشان می‌دهد، و بخش دوم مصرف حافظه توسط کِلِرک‌های مختلف (اجزای داخلی) SQL Server را به تفکیک نوع نمایش می‌دهد، که در بهینه‌سازی حافظه SQL Server بسیار کاربردی است.

3. نظارت بر فضای دیسک

پر شدن فضای دیسک می‌تواند باعث از کار افتادن پایگاه داده شود. پایش منظم فضای دیسک، به خصوص برای فایل‌های داده، فایل‌های لاگ و فایل‌های tempdb، ضروری است تا از بروز چنین مشکلاتی جلوگیری شود.

برای بررسی فضای خالی دیسک‌های سرور، از اسکریپت زیر بهره ببرید:

EXEC xp_fixeddrives;

SELECT
    DB_NAME(a.database_id) AS DatabaseName,
    a.file_id,
    name AS FileName,
    CAST(size / 128.0 AS DECIMAL(10, 2)) AS CurrentSizeMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) AS SpaceUsedMB,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) / 128.0 AS DECIMAL(10, 2)) AS FreeSpaceMB,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 100.0 / size AS DECIMAL(10, 2)) AS FreeSpacePercent,
    physical_name AS PhysicalFileName
FROM sys.master_files a WITH (NOLOCK)
ORDER BY FreeSpacePercent DESC;

این اسکریپت نه تنها فضای دیسک‌های فیزیکی را نشان می‌دهد، بلکه جزئیات فضای مصرف شده و آزاد برای هر فایل پایگاه داده (شامل دیتابیس‌های کاربر و سیستمی) را نیز نمایش می‌دهد که برای مدیریت ظرفیت ذخیره‌سازی بسیار مهم است.

4. شناسایی فرآیندهای مسدود شده (Blocking)

Blocking یکی از رایج‌ترین مشکلات عملکردی است که می‌تواند باعث کندی یا توقف کامل تراکنش‌ها شود. این اسکریپت به شما کمک می‌کند تا به سرعت سرشاخه blocking را پیدا کرده و آن را برطرف کنید.

برای شناسایی تراکنش‌های مسدود شده، از کوئری زیر استفاده کنید:

SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_status,
    t1.request_session_id AS blocking_session_id,
    t2.blocking_session_id AS blocked_by_session_id,
    t1.request_owner_type,
    t1.request_owner_guid,
    t1.request_cpu_time,
    t1.request_wait_time,
    t1.request_waittype,
    t1.request_last_wait_type,
    t1.request_start_time,
    t1.request_request_id,
    DB_NAME(t1.resource_database_id) AS DatabaseName,
    s1.program_name AS BlockingProgram,
    s1.host_name AS BlockingHost,
    s1.login_name AS BlockingLogin,
    s2.program_name AS BlockedProgram,
    s2.host_name AS BlockedHost,
    s2.login_name AS BlockedLogin,
    t2.command,
    t2.last_wait_type,
    t2.wait_type,
    t2.wait_time,
    t2.last_batch,
    t2.text,
    t2.query_plan
FROM sys.dm_tran_locks t1 WITH (NOLOCK)
JOIN sys.dm_exec_requests t2 WITH (NOLOCK) ON t1.request_session_id = t2.session_id
LEFT JOIN sys.dm_exec_sessions s1 WITH (NOLOCK) ON t1.request_session_id = s1.session_id
LEFT JOIN sys.dm_exec_sessions s2 WITH (NOLOCK) ON t2.blocking_session_id = s2.session_id
WHERE t1.request_status = 'WAIT' AND t2.blocking_session_id IS NOT NULL;

این کوئری جزئیات کاملی از قفل‌ها، شامل نوع منبع، دیتابیس، نوع درخواست، و اطلاعات سشن‌های مسدودکننده و مسدود شده را ارائه می‌دهد. این اطلاعات برای ریشه‌یابی و حل مشکلات blocking SQL Server ضروری است.

5. کشف Deadlock ها (بُن‌بست)

Deadlock‌ها شرایطی پیچیده‌تر از blocking هستند که در آن دو یا چند فرآیند به صورت متقابل منتظر منابعی هستند که توسط دیگری قفل شده است، و در نتیجه هیچ‌کدام نمی‌توانند ادامه پیدا کنند. SQL Server به طور خودکار یکی از آن‌ها را به عنوان “deadlock victim” انتخاب کرده و خاتمه می‌دهد. مانیتورینگ deadlock برای بهبود طراحی کوئری و ایندکس‌ها حیاتی است.

برای مشاهده رویدادهای deadlock که در System Health Session ثبت شده‌اند، از کوئری زیر استفاده کنید (این نیاز به دسترسی به XML لاگ دارد):

SELECT
    CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

این کوئری گزارش‌های XML deadlock را از Extended Events Session `system_health` بازیابی می‌کند. تحلیل این گزارش‌ها (که معمولاً با گراف deadlock نمایش داده می‌شوند) به شما کمک می‌کند تا الگوهای deadlock را در پایگاه داده SQL Server خود شناسایی و برطرف کنید.

6. بررسی fragmentation ایندکس‌ها

fragmentation ایندکس می‌تواند به شدت بر عملکرد کوئری‌ها تأثیر بگذارد، زیرا باعث می‌شود SQL Server برای خواندن داده‌ها، صفحات بیشتری را از دیسک بخواند. این اسکریپت به شما کمک می‌کند تا ایندکس‌های با fragmentation بالا را شناسایی کنید.

برای بررسی سطح fragmentation ایندکس‌های دیتابیس، از این کوئری استفاده کنید:

SELECT
    DB_NAME(ps.database_id) AS DatabaseName,
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_type_desc,
    ps.avg_fragmentation_in_percent,
    ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
    AND ps.avg_fragmentation_in_percent > 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

این کوئری ایندکس‌هایی را که در دیتابیس فعلی شما بیش از 10 درصد fragmentation دارند، لیست می‌کند. شناسایی و بازسازی یا مرتب‌سازی ایندکس‌های фрагمنت شده، برای افزایش سرعت اجرای کوئری‌ها و بهبود عملکرد کلی SQL Server ضروری است.

7. مانیتورینگ وضعیت بکاپ‌ها

اطمینان از موفقیت‌آمیز بودن بکاپ‌ها و به‌روز بودن آن‌ها، حیاتی‌ترین بخش از استراتژی ریکاوری فاجعه است. این اسکریپت وضعیت آخرین بکاپ‌های گرفته شده برای هر پایگاه داده را نمایش می‌دهد.

برای بررسی وضعیت آخرین بکاپ‌های گرفته شده، کوئری زیر را اجرا کنید:

SELECT
    d.name AS DatabaseName,
    COALESCE(MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), 'N/A') AS LastFullBackup,
    COALESCE(MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), 'N/A') AS LastDifferentialBackup,
    COALESCE(MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), 'N/A') AS LastLogBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY d.name
ORDER BY d.name;

این کوئری اطلاعات آخرین زمان بکاپ کامل (Full)، تفاضلی (Differential) و لاگ (Log) را برای هر دیتابیس SQL Server نمایش می‌دهد. پایش منظم این اطلاعات به شما اطمینان می‌دهد که در صورت بروز هرگونه مشکل، داده‌های شما قابل بازیابی هستند و از از دست رفتن اطلاعات جلوگیری می‌کند.

نتیجه‌گیری: ابزاری برای پایش مستمر SQL Server

این هفت اسکریپت قدرتمند، یک نقطه شروع عالی برای استراتژی جامع مانیتورینگ SQL Server شما هستند. با اجرای منظم این اسکریپت‌ها، می‌توانید به طور فعال سلامت و عملکرد پایگاه داده خود را پایش کرده، مشکلات احتمالی را پیش از آنکه به بحران تبدیل شوند شناسایی کنید و یک محیط SQL Server پایدار و بهینه را تضمین نمایید. برای بهترین نتایج، این اسکریپت‌ها را در سیستم‌های پایش خودکار ادغام کرده و آستانه‌های هشدار مناسب را تنظیم کنید. با استفاده از این ابزارها، مدیریت SQL Server شما کارآمدتر و مطمئن‌تر خواهد بود.