بهینه سازی عملکرد SQL Server با ایندکس فیلتر شده چرخشی(Rolling Filtered Index)

ایندکس فیلتر شده چرخشی(Rolling Filtered Index) در SQL Server: راهکاری نوین برای عملکرد بهینه پایگاه داده

ایندکس‌های فیلتر شده (Filtered Indexes) یکی از قدرتمندترین ویژگی‌ها در SQL Server هستند که نقش مهمی در بهینه‌سازی پایگاه داده ایفا می‌کنند. با کاهش اندازه ایندکس و متمرکز کردن آن بر روی زیرمجموعه خاصی از داده‌ها، ایندکس فیلتر شده می‌تواند به طرز چشمگیری عملکرد کوئری‌هایی را بهبود بخشد که بر اساس همان شرط فیلتر شده عمل می‌کنند.

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

راهکار “ایندکس فیلتر شده چرخشی” (Rolling Filtered Index) برای مقابله با این چالش طراحی شده است. این رویکرد شامل ایجاد چندین ایندکس فیلتر شده است که هر یک بازه زمانی خاص و غیرهمپوشانی از داده‌ها را پوشش می‌دهند. به این ترتیب، با ورود داده‌های جدید و قدیمی شدن داده‌های قبلی، قدیمی‌ترین ایندکس حذف شده و یک ایندکس جدید برای جدیدترین بازه زمانی ایجاد می‌شود. این فرآیند چرخشی، سربار نگهداری ایندکس را توزیع کرده و تضمین می‌کند که ایندکس‌ها همیشه بر روی مرتبط‌ترین و فعال‌ترین داده‌ها متمرکز باقی بمانند.

برای درک بهتر این مفهوم، سناریویی را در نظر بگیرید که یک جدول بزرگ با نام `Orders` (سفارشات) دارید. بیشتر کوئری‌های مهم و پراستفاده، سفارشات “فعال” را هدف قرار می‌دهند. فرض کنید “سفارشات فعال” به آنهایی اطلاق می‌شود که در ۳۰ روز گذشته ثبت شده‌اند. برای شروع، بیایید یک جدول نمونه ایجاد کنیم:

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    OrderDate DATETIME,
    Status VARCHAR(50),
    Amount DECIMAL(18,2)
);

پس از ایجاد جدول، با استفاده از کد زیر، داده‌های نمونه‌ای را به جدول `Orders` اضافه می‌کنیم. این داده‌ها شامل سفارشاتی با تاریخ‌های مختلف و وضعیت‌های `Active` یا `Shipped` هستند. بخشی از سفارشات اخیر را نیز به صورت `Active` به‌روزرسانی می‌کنیم تا سناریوی مورد نظر محقق شود و بتوانیم راهکار ایندکس فیلتر شده چرخشی را برای بهینه‌سازی عملکرد دیتابیس پیاده‌سازی کنیم.

-- Insert some sample data
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO Orders (OrderId, OrderDate, Status, Amount)
    VALUES (
        @i,
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
        CASE WHEN ABS(CHECKSUM(NEWID())) % 2 = 0 THEN 'Active' ELSE 'Shipped' END,
        ABS(CHECKSUM(NEWID())) % 1000 + 100.00
    );
    SET @i = @i + 1;
END;

-- Make some recent orders active
UPDATE Orders
SET Status = 'Active'
WHERE OrderDate >= DATEADD(day, -30, GETDATE()) AND Status <> 'Active';

اگر یک ایندکس فیلتر شده واحد بر روی `Status = ‘Active’` و `OrderDate > DATEADD(day, -30, GETDATE())` ایجاد کنیم، این ایندکس باید تقریباً هر روز با تغییر `GETDATE()` بازسازی یا حداقل دوباره سازماندهی شود، در غیر این صورت کارایی خود را از دست داده و به ابزاری برای بهینه‌سازی کوئری تبدیل نخواهد شد. این سربار نگهداری مکرر، هدف اصلی بهینه‌سازی را نقض می‌کند. کد زیر یک مثال از چنین ایندکس سنتی است:

CREATE NONCLUSTERED INDEX IX_Orders_Active_Status_Date
ON Orders (OrderDate)
WHERE Status = 'Active' AND OrderDate > DATEADD(day, -30, GETDATE());

برای حل این مشکل و بهره‌مندی واقعی از ایندکس فیلتر شده در SQL Server، رویکرد چرخشی را به کار می‌بریم. به جای یک ایندکس پویا، چندین ایندکس فیلتر شده ایجاد می‌کنیم که هر یک برای یک پنجره زمانی ثابت و بدون همپوشانی است. به عنوان مثال، می‌توانیم چهار پنجره ۷ روزه را برای پوشش ۲۸ روز گذشته در نظر بگیریم. این امر نگهداری ایندکس را ساده‌تر و بهینه‌تر می‌کند.

ابتدا، اگر ایندکس سنتی که قبلاً ایجاد کردیم وجود دارد، آن را حذف می‌کنیم تا بتوانیم ایندکس‌های چرخشی را پیاده‌سازی کنیم:

IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Orders_Active_Status_Date' AND object_id = OBJECT_ID('Orders'))
BEGIN
    DROP INDEX IX_Orders_Active_Status_Date ON Orders;
END

سپس، چهار ایندکس فیلتر شده اولیه را ایجاد می‌کنیم که هر یک یک بازه زمانی ۷ روزه از سفارشات فعال را پوشش می‌دهند. این ایندکس‌ها به ترتیب برای ۷ روز گذشته، ۷ تا ۱۴ روز گذشته، ۱۴ تا ۲۱ روز گذشته و ۲۱ تا ۲۸ روز گذشته هستند. این مجموعه ایندکس‌های اولیه، نقطه شروع پیاده‌سازی ایندکس فیلتر شده چرخشی را تشکیل می‌دهند.

CREATE NONCLUSTERED INDEX IX_Orders_Active_Period1 ON Orders (OrderDate)
WHERE Status = 'Active' AND OrderDate >= DATEADD(day, -7, GETDATE()) AND OrderDate < GETDATE();

CREATE NONCLUSTERED INDEX IX_Orders_Active_Period2 ON Orders (OrderDate)
WHERE Status = 'Active' AND OrderDate >= DATEADD(day, -14, GETDATE()) AND OrderDate < DATEADD(day, -7, GETDATE());

CREATE NONCLUSTERED INDEX IX_Orders_Active_Period3 ON Orders (OrderDate)
WHERE Status = 'Active' AND OrderDate >= DATEADD(day, -21, GETDATE()) AND OrderDate < DATEADD(day, -14, GETDATE());

CREATE NONCLUSTERED INDEX IX_Orders_Active_Period4 ON Orders (OrderDate)
WHERE Status = 'Active' AND OrderDate >= DATEADD(day, -28, GETDATE()) AND OrderDate < DATEADD(day, -21, GETDATE());

برای مدیریت خودکار ایندکس‌های چرخشی، از یک روال ذخیره شده (Stored Procedure) استفاده می‌کنیم که می‌توان آن را به صورت روزانه از طریق SQL Server Agent زمان‌بندی کرد. این روال، ستون فقرات استراتژی بهینه‌سازی ایندکس ما را تشکیل می‌دهد. این رویکرد تضمین می‌کند که ایندکس‌ها همیشه روی جدیدترین داده‌های فعال متمرکز باشند بدون نیاز به بازسازی مکرر ایندکس‌های موجود. هر بار که این روال اجرا می‌شود (که توصیه می‌شود روزانه باشد):

1. ایندکسی را که قدیمی‌ترین بازه زمانی داده‌ها (مثلاً بازه ۳۵ تا ۲۸ روز پیش) را پوشش می‌دهد، شناسایی و حذف می‌کند.
2. سپس یک ایندکس کاملاً جدید برای جدیدترین بازه زمانی داده‌ها (۷ روز گذشته تا امروز) ایجاد می‌کند.

نام‌گذاری ایندکس‌ها شامل تاریخ شروع و پایان بازه است تا شناسایی و مدیریت آن‌ها آسان‌تر باشد و رویکرد “چرخشی” به طور مؤثر پیاده‌سازی شود. این راهکار، گامی مهم در جهت بهینه‌سازی SQL Server و افزایش کارایی پایگاه داده است.

CREATE PROCEDURE dbo.MaintainRollingFilteredIndexes
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @today DATE = CAST(GETDATE() AS DATE);
    
    -- Define the oldest range to drop (e.g., 28-35 days ago)
    DECLARE @oldestDropIndexStartDate DATE = DATEADD(day, -35, @today);
    DECLARE @oldestDropIndexEndDate DATE = DATEADD(day, -28, @today);
    DECLARE @oldestDropIndexName NVARCHAR(256) = N'IX_Orders_Active_' + FORMAT(@oldestDropIndexStartDate, 'yyyyMMdd') + N'_' + FORMAT(@oldestDropIndexEndDate, 'yyyyMMdd');

    -- Define the newest range to create (e.g., 0-7 days ago)
    DECLARE @newCreateIndexStartDate DATE = DATEADD(day, -7, @today);
    DECLARE @newCreateIndexEndDate DATE = @today;
    DECLARE @newCreateIndexName NVARCHAR(256) = N'IX_Orders_Active_' + FORMAT(@newCreateIndexStartDate, 'yyyyMMdd') + N'_' + FORMAT(@newCreateIndexEndDate, 'yyyyMMdd');

    -- Drop the oldest index if it exists
    DECLARE @sqlDrop NVARCHAR(MAX);
    SET @sqlDrop = N'IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''' + @oldestDropIndexName + ''' AND object_id = OBJECT_ID(''Orders'')) ' +
                   N'DROP INDEX ' + QUOTENAME(@oldestDropIndexName) + N' ON Orders;';
    EXEC sp_executesql @sqlDrop;

    -- Create the new index
    DECLARE @sqlCreate NVARCHAR(MAX);
    SET @sqlCreate = N'CREATE NONCLUSTERED INDEX ' + QUOTENAME(@newCreateIndexName) + N' ON Orders (OrderDate) ' +
                     N'WHERE Status = ''Active'' AND OrderDate >= ''' + CONVERT(NVARCHAR(10), @newCreateIndexStartDate, 120) + N''' AND OrderDate < ''' + CONVERT(NVARCHAR(10), @newCreateIndexEndDate, 120) + N''';';
    EXEC sp_executesql @sqlCreate;
END;

مزیت اصلی این روش این است که سربار بازسازی ایندکس به جای یک عملیات بزرگ و پرهزینه، به عملیات‌های کوچک‌تر حذف و ایجاد روزانه توزیع می‌شود. این توزیع سربار به این معناست که سیستم شما کمتر تحت فشار قرار می‌گیرد و عملکرد کلی پایگاه داده پایدارتر باقی می‌ماند. همچنین، این رویکرد تضمین می‌کند که ایندکس‌های فعال همیشه کوچک‌تر، متمرکزتر و بسیار مرتبط با جدیدترین داده‌ها هستند، که به شدت بهینه‌سازی کوئری (Query Optimization) را بهبود می‌بخشد.

با این حال، مانند هر استراتژی بهینه‌سازی دیتابیس، ملاحظاتی نیز وجود دارد که باید در نظر گرفته شوند. این ملاحظات شامل مدیریت صحیح تکه‌تکه شدن ایندکس (Index Fragmentation)، نظارت بر تأثیرات بر لاگ تراکنش (Transaction Log)، مدیریت تعداد کلی ایندکس‌ها در سیستم، و درک عمیق الگوهای کوئری شما است. پیاده‌سازی دقیق و آزمایش کافی برای اطمینان از سازگاری کامل با محیط شما حیاتی است.

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