بهینه سازی عملکرد SQL Server مدیریت ایندکس‌های بلااستفاده در Availability Groups

بهینه‌سازی عملکرد SQL Server: مدیریت ایندکس‌های بلااستفاده در گروه‌های دسترس‌پذیری (Availability Groups)

در بخش اول این مجموعه مقاله، ما به مفهوم ایندکس‌های بلااستفاده و اینکه چگونه می‌توانند بر عملکرد پایگاه داده‌های SQL Server شما تأثیر منفی بگذارند، پرداختیم. در این مقاله، به بررسی یک اسکریپت می‌پردازیم که می‌تواند ایندکس‌های بلااستفاده را به طور مؤثر، حتی در گروه‌های دسترس‌پذیری (Availability Groups)، مدیریت کند. این اسکریپت برای کمک به مهندسان پایگاه داده طراحی شده است تا با پایش، غیرفعال کردن و یا حذف ایندکس‌هایی که سربار غیرضروری ایجاد می‌کنند، عملکرد سیستم را بهبود بخشند.

مبانی مدیریت ایندکس‌های بلااستفاده

این راه حل شامل چندین جزء ضروری برای عملکرد صحیح خود است. اولین جزء، یک روال ذخیره شده (stored procedure) سیستمی برای بررسی اینکه آیا یک ایندکس می‌تواند به طور ایمن حذف یا غیرفعال شود، می‌باشد. این امکان را به پایگاه داده می‌دهد تا بررسی کند که آیا ایندکس برای عملیات حذف (DROP) یا غیرفعال‌سازی (DISABLE) معتبر است یا خیر. این کار از انجام عملیات توسط پایگاه داده در صورت پیکربندی فعلی برای Replication، به عنوان مثال، جلوگیری می‌کند و اطمینان می‌دهد که اقدامات نگهداری ایندکس باعث شکست وابستگی‌های بحرانی سیستم نمی‌شود. روال ذخیره شده سیستمی که باید استفاده شود، `sys.sp_can_sql_drop_object` است.

EXEC sys.sp_can_sql_drop_object @object_id = @IndexID, @database_id = @DatabaseID, @check_is_referenced = 0;

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

CREATE TABLE [dbo].[UnderUsedIndexLog](
	[UnderUsedIndexLogID] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[SchemaName] [sysname] NOT NULL,
	[TableName] [sysname] NOT NULL,
	[IndexName] [sysname] NOT NULL,
	[IndexID] [int] NOT NULL,
	[Action] [nvarchar](50) NOT NULL,
	[ActionDate] [datetime] NOT NULL,
 CONSTRAINT [PK_UnderUsedIndexLog] PRIMARY KEY CLUSTERED 
(
	[UnderUsedIndexLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

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

معرفی روال ذخیره شده usp_FindUnderUsedIndexesAG

این روال ذخیره شده اصلی برای شناسایی و مدیریت ایندکس‌های بلااستفاده طراحی شده است. ساختار کلی آن به شرح زیر است:

CREATE PROCEDURE [dbo].[usp_FindUnderUsedIndexesAG]
(
	@MinUsageThreshold INT = 0,
	@RetentionDays INT = 30,
	@DatabaseName SYSNAME = NULL,
	@IndexName SYSNAME = NULL,
	@DisableIndexes BIT = 0,
	@DropIndexes BIT = 0,
	@WhatIf BIT = 1,
	@AvailabilityGroupOnly BIT = 0,
	@ListDatabases BIT = 0
)
AS
BEGIN
	SET NOCOUNT ON;
	-- Stored procedure logic goes here
END;

این روال ذخیره شده چندین پارامتر ورودی برای سفارشی‌سازی نحوه شناسایی و مدیریت ایندکس‌ها می‌پذیرد:

* **@MinUsageThreshold**: این پارامتر حداقل آستانه استفاده از ایندکس را تعیین می‌کند. ایندکس‌هایی که میزان استفاده (جستجو، اسکن، lookup) آن‌ها کمتر یا مساوی با این مقدار باشد، به عنوان بلااستفاده در نظر گرفته می‌شوند. پیش‌فرض آن 0 است، به این معنی که ایندکس‌هایی با هیچ‌گونه استفاده خواندنی (read usage) شناسایی می‌شوند.
* **@RetentionDays**: تعداد روزهایی را که یک ایندکس باید قبل از در نظر گرفتن آن برای غیرفعال‌سازی یا حذف در لاگ حضور داشته باشد، مشخص می‌کند. این یک دوره محافظه‌کارانه را برای جمع‌آوری داده‌های استفاده تضمین می‌کند و از تصمیم‌گیری زودهنگام جلوگیری می‌کند. پیش‌فرض آن 30 روز است.
* **@DatabaseName**: به شما امکان می‌دهد تا ایندکس‌های بلااستفاده را در یک پایگاه داده خاص جستجو کنید. اگر NULL باشد، تمامی پایگاه داده‌ها را بررسی می‌کند.
* **@IndexName**: برای جستجوی یک ایندکس خاص در نظر گرفته شده است. اگر NULL باشد، تمامی ایندکس‌ها در پایگاه داده(های) مورد نظر را بررسی می‌کند.
* **@DisableIndexes**: یک پرچم BIT است که اگر روی 1 تنظیم شود، ایندکس‌های بلااستفاده شناسایی شده را غیرفعال می‌کند. این یک راه حل موقت است که به شما امکان می‌دهد تأثیر غیرفعال‌سازی را بدون حذف دائمی ایندکس ارزیابی کنید.
* **@DropIndexes**: یک پرچم BIT است که اگر روی 1 تنظیم شود، ایندکس‌های بلااستفاده شناسایی شده را حذف می‌کند. این یک اقدام دائمی‌تر است که باید با دقت استفاده شود.
* **@WhatIf**: یک پرچم BIT است که اگر روی 1 تنظیم شود، روال ذخیره شده فقط عملیات برنامه‌ریزی شده را گزارش می‌دهد و در واقع هیچ تغییری ایجاد نمی‌کند. این برای اهداف تست و برنامه‌ریزی بسیار مفید است. پیش‌فرض آن 1 است، برای جلوگیری از تغییرات ناخواسته.
* **@AvailabilityGroupOnly**: یک پرچم BIT است که اگر روی 1 تنظیم شود، جستجو را فقط به پایگاه داده‌هایی که بخشی از یک گروه دسترس‌پذیری (Availability Group) هستند، محدود می‌کند. این برای محیط‌هایی با AGs بسیار مفید است.
* **@ListDatabases**: یک پرچم BIT است که اگر روی 1 تنظیم شود، فقط پایگاه داده‌هایی را که در این روال ذخیره شده در نظر گرفته شده‌اند، فهرست می‌کند. این برای اشکال‌زدایی و درک دامنه عملیات مفید است.

منطق شناسایی و مدیریت ایندکس‌ها

این روال ذخیره شده طوری طراحی شده است که به ساده‌ترین شکل ممکن، ایندکس‌های بلااستفاده را شناسایی کند. منطق اصلی را می‌توان به چندین بخش کلیدی تقسیم کرد:

1. **حلقه زدن در هر پایگاه داده**: روال ذخیره شده در تمامی پایگاه داده‌ها یا پایگاه داده‌های مشخص شده حلقه می‌زند تا ایندکس‌ها را بررسی کند.
2. **بررسی میزان استفاده از ایندکس**: هسته منطق، میزان استفاده از ایندکس را با نگاه کردن به آمارهای زیر از نمایش مدیریت دینامیک (DMV) `sys.dm_db_index_usage_stats` بررسی می‌کند:
* `user_seeks`: تعداد جستجوهای مستقیم روی ایندکس.
* `user_scans`: تعداد اسکن‌های جدول یا ایندکس.
* `user_lookups`: تعداد جستجوهای دقیق (bookmark lookups) برای بازیابی داده‌های بیشتر.
* `user_updates`: تعداد به‌روزرسانی‌ها (درج، حذف، به‌روزرسانی) روی ایندکس.

برای تعیین “استفاده” از ایندکس (عملکرد خواندن)، روال ذخیره شده یک محاسبه ساده انجام می‌دهد:

IndexUsage = user_seeks + user_scans + user_lookups

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

IndexUpdates = user_updates

این فرمول تعداد به‌روزرسانی‌های کاربر را برای یک ایندکس نشان می‌دهد. اگر IndexUsage کمتر یا مساوی با پارامتر `@MinUsageThreshold` باشد (که نشان می‌دهد ایندکس بلااستفاده است) و IndexUpdates بزرگتر یا مساوی با پارامتر `@MinUsageThreshold` باشد (که نشان می‌دهد ایندکس به میزان زیادی به‌روزرسانی شده و به طور بالقوه منابعی را بر روی ایندکسی که زیاد خوانده نمی‌شود هدر می‌دهد)، آنگاه ایندکس به عنوان یک کاندید برای مدیریت در نظر گرفته می‌شود.

3. **تعیین وضعیت replica**: نکته مهم بعدی این است که تعیین کنیم آیا ایندکس در یک replica اصلی (primary) یا ثانویه (secondary) در یک گروه دسترس‌پذیری (Availability Group) قرار دارد. این بررسی با استفاده از DMV (نمایش مدیریت دینامیک) `sys.dm_hadr_database_replica_states` انجام می‌شود. اگر `replica_role` برابر با 1 باشد، به این معنی است که یک replica اصلی است. این مهم است زیرا ایندکس‌ها معمولاً فقط در replica اصلی می‌توانند غیرفعال یا حذف شوند.
4. **بررسی لاگ ایندکس**: همچنین مهم است که بررسی شود آیا یک ایندکس قبلاً توسط این روال ذخیره شده، غیرفعال یا حذف شده است. این کار با مشورت جدول `UnderUsedIndexLog` انجام می‌شود و از پردازش مجدد ایندکس‌ها در یک بازه زمانی مشخص (تعیین شده توسط `@RetentionDays`) جلوگیری می‌کند.
5. **عملیات غیرفعال‌سازی یا حذف**:
* اگر پارامتر `@DisableIndexes` روی 1 تنظیم شده باشد و ایندکس معیارها را برآورده کند، روال ذخیره شده ایندکس را غیرفعال خواهد کرد. این عملیات توسط `ALTER INDEX DISABLE` انجام می‌شود و سپس به عنوان یک رکورد “Disable” در جدول `UnderUsedIndexLog` ثبت خواهد شد.
* اگر پارامتر `@DropIndexes` روی 1 تنظیم شده باشد و ایندکس معیارها را برآورده کند، روال ذخیره شده ایندکس را حذف خواهد کرد. این عملیات توسط `DROP INDEX` انجام می‌شود و سپس به عنوان یک رکورد “Drop” در جدول `UnderUsedIndexLog` ثبت خواهد شد.
* اگر پارامتر `@WhatIf` روی 1 باشد، روال ذخیره شده فقط گزارشی از اقدامات پیشنهادی را ارائه می‌دهد بدون اینکه هیچ تغییری در ایندکس‌ها ایجاد کند. این به مدیران اجازه می‌دهد تا تأثیرات احتمالی را قبل از اعمال هر گونه تغییر واقعی، بررسی کنند.

مثال‌های کاربردی

در اینجا چند مثال از نحوه استفاده از روال ذخیره شده `usp_FindUnderUsedIndexesAG` آورده شده است:

* برای فهرست کردن تمامی ایندکس‌های بلااستفاده در تمامی پایگاه داده‌ها در گروه‌های دسترس‌پذیری (Availability Groups) که در آن‌ها IndexUsage برابر 0 و IndexUpdates بیشتر از 100 است (فقط گزارش، بدون تغییر):

EXEC [dbo].[usp_FindUnderUsedIndexesAG]
    @MinUsageThreshold = 0,
    @AvailabilityGroupOnly = 1,
    @WhatIf = 1;

* برای غیرفعال کردن تمامی ایندکس‌های بلااستفاده در تمامی پایگاه داده‌ها در گروه‌های دسترس‌پذیری (Availability Groups) که در آن‌ها IndexUsage برابر 0 و IndexUpdates بیشتر از 100 است (با اعمال تغییر):

EXEC [dbo].[usp_FindUnderUsedIndexesAG]
    @MinUsageThreshold = 0,
    @AvailabilityGroupOnly = 1,
    @DisableIndexes = 1,
    @WhatIf = 0;

* برای حذف تمامی ایندکس‌های بلااستفاده در تمامی پایگاه داده‌ها در گروه‌های دسترس‌پذیری (Availability Groups) که در آن‌ها IndexUsage برابر 0 و IndexUpdates بیشتر از 100 است (با اعمال تغییر):

EXEC [dbo].[usp_FindUnderUsedIndexesAG]
    @MinUsageThreshold = 0,
    @AvailabilityGroupOnly = 1,
    @DropIndexes = 1,
    @WhatIf = 0;

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