جمع آوری خودکار اطلاعات SQL Server ردیابی تغییرات و عملکرد

راهنمای جامع جمع‌آوری خودکار اطلاعات سرور SQL: ردیابی تغییرات و تاریخچه عملکردی

این مقاله به ساخت مجموعه‌ای از جداول و روال‌های ذخیره‌شده برای جمع‌آوری اطلاعات پیکربندی و ردیابی تغییرات از نمونه‌های SQL Server می‌پردازد. در این بخش، روال‌های ذخیره‌شده را گسترش می‌دهیم تا بتوانیم اطلاعات مربوط به یک نمونه (instance) و پایگاه داده (یا چندین نمونه و پایگاه داده) را در طول زمان جمع‌آوری کنیم.

در این مقاله، چارچوب را برای جمع‌آوری موارد زیر گسترش خواهیم داد:

1. اطلاعات فایل‌های پایگاه داده (اندازه، فضای باقیمانده و غیره)
2. گزینه‌های پایگاه داده
3. مرتب‌سازی (collation) سرور و پایگاه داده

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

ساختار جدول و ذخیره‌سازی داده‌ها

در این بخش، ما جداول و روال‌های ذخیره‌شده را گسترش می‌دهیم تا اطلاعات بیشتری را در خود جای دهند. ما این فرآیند را برای هر سه مجموعه اطلاعاتی که در بالا ذکر شد، انجام خواهیم داد. بیایید با تغییرات پایگاه داده شروع کنیم. برای ذخیره اطلاعات مربوط به هر فایل پایگاه داده، یک جدول جدید به نام `dbo.ServerInfo_DBFiles` ایجاد می‌کنیم. این جدول شامل اطلاعاتی مانند نام فایل، مسیر فایل، اندازه، و فضای استفاده شده خواهد بود.

CREATE TABLE dbo.ServerInfo_DBFiles
(
    ServerInfo_DBFiles_ID INT IDENTITY(1,1) NOT NULL,
    ServerInfo_Databases_ID INT NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Physical_Name NVARCHAR(260) NOT NULL,
    Type_Desc NVARCHAR(60) NOT NULL,
    Size_MB INT NOT NULL,
    Space_Used_MB INT NOT NULL,
    Free_Space_MB INT NOT NULL,
    Free_Space_Percent DECIMAL(5,2) NOT NULL,
    Collection_Time DATETIME NOT NULL
)

این جدول به ما اجازه می‌دهد تا جزئیات هر فایل پایگاه داده را همراه با زمان جمع‌آوری آن ذخیره کنیم. ستون `ServerInfo_Databases_ID` به جدول اصلی پایگاه داده ارجاع می‌دهد.

در ادامه، جدول `dbo.ServerInfo_Databases` را تغییر می‌دهیم تا شامل ستون `Collation` باشد. این ستون برای ذخیره collation پایگاه داده استفاده می‌شود که هنگام جمع‌آوری اطلاعات برای هر پایگاه داده خاص، به این جدول اضافه خواهد شد.

ALTER TABLE dbo.ServerInfo_Databases
ADD Collation NVARCHAR(128) NULL

سپس، برای ذخیره اطلاعات مربوط به گزینه‌های پایگاه داده، یک جدول جدید به نام `dbo.ServerInfo_DBOptions` ایجاد می‌کنیم. این جدول شامل نام گزینه و مقدار آن خواهد بود.

CREATE TABLE dbo.ServerInfo_DBOptions
(
    ServerInfo_DBOptions_ID INT IDENTITY(1,1) NOT NULL,
    ServerInfo_Databases_ID INT NOT NULL,
    DBOptionName NVARCHAR(200) NOT NULL,
    DBOptionValue NVARCHAR(MAX) NOT NULL,
    Collection_Time DATETIME NOT NULL
)

این جدول برای پیگیری تغییرات در گزینه‌های پیکربندی پایگاه داده در طول زمان ضروری است.

به‌روزرسانی روال‌های ذخیره‌شده برای جمع‌آوری داده‌ها

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

در ابتدای روال ذخیره‌شده، همچنان پارامترهای اصلی و اعلان متغیرها را داریم:

CREATE PROCEDURE dbo.usp_GatherServerInformation
    @InstanceName NVARCHAR(200),
    @Databases NVARCHAR(MAX) = NULL,
    @Debug BIT = 0
AS
SET NOCOUNT ON;

DECLARE @DatabaseName NVARCHAR(200);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @CollectionTime DATETIME = GETDATE();
DECLARE @ServerInfo_ID INT;
DECLARE @ServerInfo_Databases_ID INT;
DECLARE @IsPrimary BIT;

اکنون، منطق جمع‌آوری اطلاعات فایل‌ها و گزینه‌ها را در بخش `FOR XML PATH` برای هر پایگاه داده اضافه می‌کنیم. این رویکرد به ما امکان می‌دهد تا اطلاعات تفصیلی را به صورت یکجا برای هر پایگاه داده جمع‌آوری کنیم.

SET @SQL = N'
INSERT INTO dbo.ServerInfo_DBFiles
    (ServerInfo_Databases_ID, Name, Physical_Name, Type_Desc, Size_MB, Space_Used_MB, Free_Space_MB, Free_Space_Percent, Collection_Time)
SELECT
    @ServerInfo_Databases_ID,
    sf.name,
    sf.physical_name,
    sf.type_desc,
    sf.size_mb,
    sf.space_used_mb,
    sf.free_space_mb,
    sf.free_space_percent,
    @CollectionTime
FROM
    (SELECT
        name,
        physical_name,
        type_desc,
        (size*8)/1024 AS size_mb,
        CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS INT) AS space_used_mb,
        ((size*8)/1024) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS INT) AS free_space_mb,
        CAST(CAST(((size*8)/1024) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS INT) AS DECIMAL(10,2)) / CAST((size*8)/1024 AS DECIMAL(10,2)) AS DECIMAL(5,2)) * 100 AS free_space_percent
    FROM ' + QUOTENAME(@DatabaseName) + '.sys.database_files) sf;

INSERT INTO dbo.ServerInfo_DBOptions
    (ServerInfo_Databases_ID, DBOptionName, DBOptionValue, Collection_Time)
SELECT
    @ServerInfo_Databases_ID,
    name,
    CONVERT(NVARCHAR(MAX), value),
    @CollectionTime
FROM ' + QUOTENAME(@DatabaseName) + '.sys.database_scoped_configurations
UNION ALL
SELECT
    @ServerInfo_Databases_ID,
    name,
    CONVERT(NVARCHAR(MAX), value),
    @CollectionTime
FROM ' + QUOTENAME(@DatabaseName) + '.sys.database_options;
';

این بلوک SQL، اطلاعات فایل‌های پایگاه داده و گزینه‌های آن را جمع‌آوری کرده و به جداول `dbo.ServerInfo_DBFiles` و `dbo.ServerInfo_DBOptions` اضافه می‌کند. ما از `sys.database_files` برای جزئیات فایل‌ها و `sys.database_scoped_configurations` و `sys.database_options` برای گزینه‌های پایگاه داده استفاده می‌کنیم.

همچنین، باید Collation پایگاه داده را به‌روزرسانی کنیم. این کار را می‌توانیم با استفاده از `UPDATE` در جدول `dbo.ServerInfo_Databases` انجام دهیم.

SET @SQL = N'
UPDATE sid
SET Collation = db.collation_name
FROM dbo.ServerInfo_Databases sid
JOIN sys.databases db ON sid.DatabaseName = db.name
WHERE sid.ServerInfo_Databases_ID = @ServerInfo_Databases_ID;
';

این دستور SQL، ستون `Collation` را در جدول `dbo.ServerInfo_Databases` برای هر پایگاه داده با مقدار صحیح آن از `sys.databases` به‌روزرسانی می‌کند.

اجرای روال ذخیره‌شده برای جمع‌آوری داده‌ها

هنگامی که تمام جداول و روال‌های ذخیره‌شده به‌روزرسانی شدند، می‌توانید روال ذخیره‌شده `usp_GatherServerInformation` را با پارامترهای مورد نظر خود اجرا کنید. برای مثال، می‌توانید تمام پایگاه‌های داده را یا فقط پایگاه‌های داده خاصی را هدف قرار دهید.

EXEC dbo.usp_GatherServerInformation @InstanceName = 'YOUR_INSTANCE_NAME', @Databases = 'AdventureWorks, Northwind', @Debug = 0;

با هر بار اجرا، داده‌های جدید جمع‌آوری شده و به صورت تاریخی در جداول ذخیره می‌شوند. این به شما امکان می‌دهد تا روندها و تغییرات را در طول زمان مشاهده کنید.

تحلیل و گزارش‌گیری از داده‌های تاریخی

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

برای دیدن رشد فایل‌ها:

SELECT
    dbf.Collection_Time,
    db.DatabaseName,
    dbf.Name AS FileName,
    dbf.Size_MB,
    dbf.Free_Space_MB,
    dbf.Free_Space_Percent
FROM
    dbo.ServerInfo_DBFiles dbf
JOIN
    dbo.ServerInfo_Databases db ON dbf.ServerInfo_Databases_ID = db.ServerInfo_Databases_ID
WHERE
    db.DatabaseName = 'YourDatabaseName'
ORDER BY
    dbf.Collection_Time ASC;

این کوئری اطلاعات فایل‌های یک پایگاه داده خاص را در طول زمان بازیابی می‌کند و به شما امکان می‌دهد رشد و مصرف فضا را مشاهده کنید.

برای بررسی تغییرات گزینه‌های پایگاه داده:

SELECT
    dbo.Collection_Time,
    db.DatabaseName,
    dbo.DBOptionName,
    dbo.DBOptionValue
FROM
    dbo.ServerInfo_DBOptions dbo
JOIN
    dbo.ServerInfo_Databases db ON dbo.ServerInfo_Databases_ID = db.ServerInfo_Databases_ID
WHERE
    db.DatabaseName = 'YourDatabaseName'
    AND dbo.DBOptionName = 'AUTO_CLOSE' -- یا هر گزینه دیگری
ORDER BY
    dbo.Collection_Time ASC;

این کوئری تغییرات یک گزینه خاص پایگاه داده را در طول زمان نشان می‌دهد که می‌تواند برای عیب‌یابی مشکلات پیکربندی بسیار مفید باشد.

نتیجه‌گیری

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