پایش و جمع‌آوری خودکار اطلاعات عملکردی SQLServer

مدیریت پیشرفته SQL Server: جمع‌آوری خودکار اطلاعات عملکردی SQLServer برای پایش و بهینه‌سازی

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

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

جمع‌آوری معیارهای کلیدی عملکرد سرور

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

(CPU_Busy_Ticks / Total_Ticks) * 100

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

برای دریافت اطلاعات مربوط به استفاده از CPU و سایر شمارنده‌های عملکردی سیستم عامل، می‌توانید از پرس و جوی SQL زیر استفاده کنید:

SELECT TOP 10
    SQLProcessUtilization AS [SQL Server Process CPU Utilization],
    OtherProcessUtilization AS [Other Process CPU Utilization],
    (100 - SystemIdle) AS [Total CPU Utilization],
    SystemIdle AS [System Idle]
FROM
    (SELECT
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SQLProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/OtherProcessUtilization)[1]', 'int') AS OtherProcessUtilization
    FROM
        (SELECT CONVERT (XML, record)
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE '%%') AS x(record)
    ) AS y
ORDER BY record_id DESC;

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

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

برای مشاهده وضعیت کلی حافظه سیستم و حافظه مصرفی SQL Server، از پرس و جوی زیر استفاده کنید:

SELECT
    total_physical_memory_kb / 1024 AS [Total Physical Memory (MB)],
    available_physical_memory_kb / 1024 AS [Available Physical Memory (MB)],
    total_page_file_kb / 1024 AS [Total Page File (MB)],
    available_page_file_kb / 1024 AS [Available Page File (MB)],
    system_memory_state_desc AS [System Memory State],
    instance_name,
    cntr_value / 1024 AS [SQL Server Memory Usage (MB)]
FROM sys.dm_os_sys_info
CROSS JOIN sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)' AND object_name LIKE '%SQLServer:Memory Manager%';

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

یکی دیگر از گلوگاه‌های رایج در عملکرد SQL Server، سیستم دیسک (Disk I/O) است. سرعت خواندن و نوشتن داده‌ها بر روی دیسک تأثیر مستقیمی بر عملکرد کوئری‌ها و تراکنش‌ها دارد. پایش دقیق I/O دیسک برای شناسایی درایوهای کند یا فایل‌هایی با فعالیت بالا حیاتی است.

برای دریافت آمارهای I/O فایل‌های پایگاه داده، شامل میزان خواندن و نوشتن و تأخیر مربوطه، از این کوئری استفاده کنید:

SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.physical_name AS PhysicalFileName,
    vfs.num_reads AS NumberOfReads,
    vfs.num_writes AS NumberOfWrites,
    vfs.io_stall_read_ms AS ReadStallMilliseconds,
    vfs.io_stall_write_ms AS WriteStallMilliseconds,
    (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS TotalStallMilliseconds,
    (vfs.num_reads + vfs.num_writes) AS TotalIOOperations,
    CASE
        WHEN (vfs.num_reads > 0) THEN (vfs.io_stall_read_ms / vfs.num_reads)
        ELSE 0
    END AS AvgReadStallMilliseconds,
    CASE
        WHEN (vfs.num_writes > 0) THEN (vfs.io_stall_write_ms / vfs.num_writes)
        ELSE 0
    END AS AvgWriteStallMilliseconds
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN
    sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY TotalStallMilliseconds DESC;

این کوئری به شما امکان می‌دهد تا فایل‌هایی با بالاترین تأخیر I/O را شناسایی کنید، که این خود سرنخی برای عیب‌یابی عملکرد دیسک و بهبود آن فراهم می‌آورد.

ثبت و تحلیل داده‌های جمع‌آوری شده

جمع‌آوری صرف اطلاعات کافی نیست؛ باید این داده‌ها به طور مؤثر ذخیره و تحلیل شوند. ایجاد یک پایگاه داده مرکزی برای ثبت این اطلاعات، امکان تحلیل روندهای تاریخی، گزارش‌گیری و هشداردهی را فراهم می‌کند. این رویکرد به شما کمک می‌کند تا تغییرات در طول زمان را مشاهده کرده و مشکلات را قبل از اینکه به بحران تبدیل شوند، شناسایی کنید.

به عنوان مثال، برای ذخیره اطلاعات CPU، می‌توانید جدولی مانند زیر ایجاد کرده و داده‌ها را به آن وارد کنید:

CREATE TABLE ServerMonitoring.dbo.CpuUsageHistory
(
    MonitorID INT IDENTITY(1,1) PRIMARY KEY,
    RecordTime DATETIME DEFAULT GETDATE(),
    SQLProcessUtilization INT,
    OtherProcessUtilization INT,
    TotalCPUUtilization INT,
    SystemIdle INT
);

INSERT INTO ServerMonitoring.dbo.CpuUsageHistory
(SQLProcessUtilization, OtherProcessUtilization, TotalCPUUtilization, SystemIdle)
SELECT TOP 1
    SQLProcessUtilization,
    OtherProcessUtilization,
    (100 - SystemIdle) AS TotalCPUUtilization,
    SystemIdle
FROM
    (SELECT
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SQLProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/OtherProcessUtilization)[1]', 'int') AS OtherProcessUtilization
    FROM
        (SELECT CONVERT (XML, record)
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE '%%') AS x(record)
    ) AS y
ORDER BY record_id DESC;

با اجرای منظم این اسکریپت‌ها و ذخیره‌سازی نتایج، می‌توانید یک مخزن داده غنی برای پایش و عیب‌یابی SQL Server ایجاد کنید و به تصمیم‌گیری‌های مبتنی بر داده برای بهبود عملکرد پایگاه داده خود بپردازید.

نتیجه‌گیری

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