بهبود عملکرد درج همزمان SQL Server با OPTIMIZE FOR SEQUENTIAL KEY

بهبود چشمگیر درج همزمان (Concurrent Inserts) در SQL Server با OPTIMIZE FOR SEQUENTIAL KEY

یکی از چالش‌های رایج در سیستم‌های مدیریت پایگاه داده، به ویژه هنگام سروکار داشتن با حجم بالای درج داده به صورت همزمان (high-concurrency inserts)، قفل‌های داخلی (latches) است. در SQL Server، این مشکل اغلب به شکل انتظار برای `PAGELATCH_EX` ظاهر می‌شود، به خصوص زمانی که چندین تراکنش سعی در درج ردیف‌ها به انتهای یک ایندکس (Index) با کلید ترتیبی (sequential key) دارند. کلیدهای ترتیبی معمولاً در جداولی که از ستون‌های `IDENTITY` یا تابع `NEWSEQUENTIALID()` برای تولید کلید اصلی (Primary Key) استفاده می‌کنند، مشاهده می‌شوند. این قفل‌ها می‌توانند منجر به کاهش شدید کارایی و کندی در درج داده شوند.

در گذشته، راهکارهایی مانند استفاده از `Trace Flag 834` (برای فعال کردن صفحات بزرگ حافظه)، `Memory-Optimized Tables` (جداول بهینه‌سازی شده برای حافظه) در OLTP درون حافظه، تکنیک‌های `Sharding` (تکه‌تکه کردن داده‌ها) یا حتی استفاده از `GUID`های غیر ترتیبی (non-sequential GUIDs) برای کاهش رقابت، مورد استفاده قرار می‌گرفتند. هر یک از این راهکارها مزایا و معایب خاص خود را داشتند و ممکن بود پیچیدگی‌هایی را به طراحی سیستم اضافه کنند یا در برخی موارد، کارایی جستجو و سایر عملیات را تحت تأثیر قرار دهند.

معرفی OPTIMIZE FOR SEQUENTIAL KEY

با معرفی SQL Server 2019 و نسخه‌های جدیدتر، یک گزینه جدید به نام `OPTIMIZE FOR SEQUENTIAL KEY` ارائه شد. این گزینه به طور خاص برای کاهش رقابت `PAGELATCH_EX` در سناریوهای درج همزمان با کلیدهای ترتیبی طراحی شده است. وقتی SQL Server می‌داند که یک دستور `INSERT` قرار است ردیف‌ها را به انتهای یک ایندکس اضافه کند، این گزینه مکانیزم قفل‌گذاری صفحات B-Tree را بهینه‌سازی می‌کند. به جای استفاده از مکانیزم قفل‌گذاری سنتی که ممکن است شامل قفل کردن چندین صفحه در یک زمان باشد، `OPTIMIZE_FOR_SEQUENTIAL_KEY` یک رویکرد قفل‌گذاری تک‌صفحه‌ای (single-page latch) را اتخاذ می‌کند که به طور چشمگیری میزان رقابت را کاهش می‌دهد.

این گزینه را می‌توان هنگام ایجاد جدول یا ایندکس، فعال کرد. در اینجا نحوه استفاده از آن آمده است:

برای ایجاد یک جدول با این گزینه:

CREATE TABLE dbo.TestTable_Optimized (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Value] VARCHAR(100)
)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

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

CREATE UNIQUE CLUSTERED INDEX IX_TestTable_Id ON dbo.TestTable_Optimized (Id)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

محدودیت‌ها و نکات استفاده

در حالی که `OPTIMIZE_FOR_SEQUENTIAL_KEY` یک ابزار قدرتمند است، مهم است که محدودیت‌های آن را درک کنید:

* **فقط برای ایندکس‌های B-Tree:** این گزینه تنها برای ایندکس‌های B-Tree (که رایج‌ترین نوع ایندکس‌ها هستند) قابل استفاده است.
* **فقط برای عملیات `INSERT`:** این بهینه‌سازی منحصراً برای عملیات درج (INSERT) طراحی شده و تأثیری بر عملکرد `UPDATE` یا `DELETE` ندارد.
* **نیاز به کلید ترتیبی شناخته‌شده:** SQL Server باید بتواند تشخیص دهد که کلید مورد استفاده برای درج، یک کلید ترتیبی است. این معمولاً برای ستون‌های `IDENTITY` یا `NEWSEQUENTIALID()` صادق است.

اثبات کارایی: یک مثال عملی

برای درک بهتر تأثیر `OPTIMIZE_FOR_SEQUENTIAL_KEY`، یک سناریوی عملی را شبیه‌سازی می‌کنیم. ابتدا یک پایگاه داده جدید ایجاد کرده و دو جدول مشابه راه‌اندازی می‌کنیم؛ یکی بدون این بهینه‌سازی و دیگری با فعال کردن آن.

**مرحله ۱: راه‌اندازی پایگاه داده و جداول تست**

یک پایگاه داده برای آزمایش ایجاد می‌کنیم:

CREATE DATABASE TestDB_SequentialKey;
GO
USE TestDB_SequentialKey;
GO

سپس دو جدول ایجاد می‌کنیم. `TestTable_NoOptimized` یک جدول استاندارد با ستون `IDENTITY` است و `TestTable_Optimized` همانند آن، اما با فعال‌سازی `OPTIMIZE_FOR_SEQUENTIAL_KEY` است:

CREATE TABLE dbo.TestTable_NoOptimized (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Value] VARCHAR(100)
);
GO

CREATE TABLE dbo.TestTable_Optimized (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Value] VARCHAR(100)
)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
GO

**مرحله ۲: اجرای تست درج همزمان (بدون بهینه‌سازی)**

برای شبیه‌سازی درج همزمان، چندین پنجره SQL Server Management Studio (SSMS) را باز کرده و در هر پنجره، اسکریپت زیر را به صورت موازی اجرا می‌کنیم. قبل از هر اجرا، مطمئن شوید که بافرهای کش پاک شده و نقطه بازرسی (checkpoint) اجرا شده است تا نتایج دقیق‌تری به دست آید.

DBCC DROPCLEANBUFFERS;
CHECKPOINT;
GO

SET NOCOUNT ON;
DECLARE @i INT = 0;
DECLARE @StartTime DATETIME = GETDATE();

WHILE @i < 1000000
BEGIN
    INSERT dbo.TestTable_NoOptimized ([Value]) VALUES ('Test');
    SET @i += 1;
END;

SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [Duration_ms_NoOptimized];
GO

پس از اتمام درج در تمام پنجره‌ها، برای مشاهده انتظارات `PAGELATCH_EX`، کوئری زیر را اجرا می‌کنیم:

SELECT
    wait_type,
    SUM(wait_time_ms) AS total_wait_time_ms,
    COUNT(*) AS total_wait_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGELATCH_EX'
GROUP BY wait_type;
GO

در یک محیط تست، مشاهده خواهید کرد که زمان اجرای کلی نسبتاً طولانی است (مثلاً چندین دقیقه بسته به سخت‌افزار) و `PAGELATCH_EX` بخش قابل توجهی از زمان انتظار را به خود اختصاص می‌دهد.

**مرحله ۳: اجرای تست درج همزمان (با بهینه‌سازی)**

حالا همین فرآیند را برای `TestTable_Optimized` تکرار می‌کنیم. ابتدا جدول را خالی کرده و دوباره بافرهای کش را پاک می‌کنیم.

TRUNCATE TABLE dbo.TestTable_Optimized;
GO
DBCC DROPCLEANBUFFERS;
CHECKPOINT;
GO

SET NOCOUNT ON;
DECLARE @i INT = 0;
DECLARE @StartTime DATETIME = GETDATE();

WHILE @i < 1000000
BEGIN
    INSERT dbo.TestTable_Optimized ([Value]) VALUES ('Test');
    SET @i += 1;
END;

SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [Duration_ms_Optimized];
GO

مجدداً، پس از اتمام درج در تمام پنجره‌ها، برای مشاهده انتظارات `PAGELATCH_EX` کوئری زیر را اجرا می‌کنیم:

SELECT
    wait_type,
    SUM(wait_time_ms) AS total_wait_time_ms,
    COUNT(*) AS total_wait_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGELATCH_EX'
GROUP BY wait_type;
GO

**نتایج و مقایسه:**

در اکثر سناریوها، مشاهده خواهید کرد که زمان اجرای درج با فعال کردن `OPTIMIZE_FOR_SEQUENTIAL_KEY` به طور چشمگیری کاهش می‌یابد (مثلاً از چند دقیقه به چند ثانیه) و میزان کل زمان انتظار برای `PAGELATCH_EX` نیز به شدت پایین می‌آید یا حتی به صفر می‌رسد. این نشان‌دهنده تأثیر مثبت و قابل توجه این گزینه بر کارایی درج همزمان است.

چه زمانی از OPTIMIZE_FOR_SEQUENTIAL_KEY استفاده کنیم؟

این گزینه زمانی بهترین عملکرد را دارد که شرایط زیر وجود داشته باشد:

* **جدول‌های با ستون `IDENTITY`:** اگر جدول شما دارای یک ستون `IDENTITY` به عنوان کلید اصلی (Primary Key) یا بخشی از یک ایندکس خوشه‌ای (Clustered Index) است.
* **استفاده از `NEWSEQUENTIALID()`:** در صورتی که برای تولید `GUID`های ترتیبی از این تابع استفاده می‌کنید و این `GUID`ها در یک ایندکس استفاده می‌شوند.
* **حجم بالای درج همزمان:** برنامه‌هایی که نیاز به درج تعداد زیادی رکورد به صورت همزمان دارند.
* **وجود `PAGELATCH_EX`:** اگر در مانیتورینگ سیستم خود، انتظارهای بالای `PAGELATCH_EX` را مشاهده می‌کنید.

چه زمانی از OPTIMIZE_FOR_SEQUENTIAL_KEY استفاده نکنیم؟

فعال کردن این گزینه در همه موارد توصیه نمی‌شود:

* **کلیدهای غیرترتیبی:** اگر ایندکس شما بر روی یک ستون با مقادیر غیرترتیبی (مانند `GUID`های رندوم، رشته‌های متنی) ساخته شده است، این گزینه بی‌تأثیر خواهد بود و حتی ممکن است کمی سربار اضافی ایجاد کند.
* **عملیات `UPDATE`/`DELETE` زیاد:** اگر جدول شما بیشتر درگیر عملیات به‌روزرسانی یا حذف است تا درج، این گزینه کمکی نخواهد کرد.
* **درج‌های نادر و کم:** در جداولی که عملیات درج به ندرت و با حجم کم انجام می‌شود، مزایای این گزینه محسوس نخواهد بود.

نتیجه‌گیری

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