راهکارهای بهینه سازی SQL Server Covering Indexes Implicit Conversions Parameter Sniffing

بهینه‌سازی عملکرد SQL Server: درک شاخص‌گذاری، Covering Indexes، تبدیل ضمنی و Parameter Sniffing

در SQL Server، شاخص‌ها (Indexes) ابزاری حیاتی برای بهبود عملکرد پایگاه داده هستند و سرعت بازیابی داده‌ها را به شکل چشمگیری افزایش می‌دهند. شاخص‌ها به عنوان یک کپی از داده‌های انتخابی از یک یا چند ستون از جدول در نظر گرفته می‌شوند که به صورت مرتب‌شده (معمولاً در ساختار B-Tree) ذخیره شده‌اند و به SQL Server امکان می‌دهند تا به سرعت به ردیف‌های خاص دسترسی پیدا کند، بدون اینکه مجبور به اسکن تمام جدول باشد. انواع مختلفی از شاخص‌ها مانند clustered، non-clustered، covering، filtered، XML، columnstore و spatial وجود دارند. در این مقاله به بررسی سه جنبه مهم از شاخص‌گذاری در SQL Server می‌پردازیم که شامل Covering Indexes، Implicit Conversions و Parameter Sniffing هستند.

**Covering Indexes**

شاخص‌های Covering نوع خاصی از شاخص‌های غیرکلاستر (non-clustered) هستند که شامل تمام ستون‌های مورد نیاز برای پاسخ به یک کوئری می‌شوند. مزیت اصلی شاخص‌های Covering این است که SQL Server می‌تواند تمام اطلاعات مورد نیاز کوئری را مستقیماً از شاخص بخواند، بدون اینکه نیازی به دسترسی به جدول اصلی (table lookup) برای بازیابی ستون‌های اضافی باشد. این کار به طور قابل توجهی عملیات I/O را کاهش داده و عملکرد کوئری را بهبود می‌بخشد.

به عنوان مثال، فرض کنید یک جدول `Persons` داریم و می‌خواهیم `FirstName`, `LastName`, و `PhoneNumber` را بر اساس `LastName` بازیابی کنیم. اگر فقط روی `LastName` یک شاخص غیرکلاستر داشته باشیم، SQL Server ابتدا از شاخص برای یافتن ردیف‌های با `LastName` مورد نظر استفاده می‌کند و سپس برای بازیابی `FirstName` و `PhoneNumber` به جدول اصلی باز می‌گردد. این عملیات اضافی که به آن “Key Lookup” یا “RID Lookup” گفته می‌شود، می‌تواند گران باشد.

برای ایجاد یک شاخص Covering، می‌توانیم تمام ستون‌های مورد نیاز کوئری را در تعریف شاخص غیرکلاستر قرار دهیم. به این صورت، شاخص به تنهایی می‌تواند تمام نیازهای کوئری را برآورده کند:

CREATE NONCLUSTERED INDEX IX_Persons_LastName_Covering
ON Persons (LastName)
INCLUDE (FirstName, PhoneNumber);

با استفاده از کلمه کلیدی `INCLUDE`، می‌توانیم ستون‌هایی را به شاخص اضافه کنیم که در کلید شاخص نیستند اما در برگه‌های (leaf nodes) شاخص ذخیره می‌شوند. این ستون‌ها به SQL Server اجازه می‌دهند تا کوئری را به طور کامل از شاخص اجرا کند.

**Implicit Conversions (تبدیل‌های ضمنی)**

تبدیل‌های ضمنی زمانی اتفاق می‌افتند که SQL Server به طور خودکار نوع داده‌ای را به نوع داده دیگری تبدیل می‌کند تا عملیات مقایسه یا تخصیص انجام شود. در حالی که این ویژگی ممکن است راحت به نظر برسد، اما می‌تواند عواقب جدی بر عملکرد شاخص‌ها داشته باشد. زمانی که SQL Server نیاز به تبدیل ضمنی یک ستون در بند `WHERE` دارد، شاخص‌های موجود روی آن ستون ممکن است غیرقابل استفاده شوند و منجر به اسکن کامل جدول (Table Scan) گردند.

مثلاً، فرض کنید یک ستون `Name` از نوع `VARCHAR` داریم و سعی می‌کنیم آن را با یک مقدار `NVARCHAR` (که با پیشوند `N` مشخص می‌شود) مقایسه کنیم.

-- ایجاد یک جدول نمونه
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- ایجاد شاخص روی ستون Name
CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees (Name);

-- درج داده
INSERT INTO Employees (EmployeeID, Name, Salary) VALUES
(1, 'John Doe', 50000),
(2, 'Jane Smith', 60000),
(3, 'Peter Jones', 75000),
(4, 'Maria Garcia', 80000);

-- کوئری با تبدیل ضمنی (NVARCHAR در مقابل VARCHAR)
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Name = N'John Doe';

در مثال بالا، اگرچه روی ستون `Name` شاخص داریم، اما SQL Server برای مقایسه `VARCHAR(Name)` با `NVARCHAR(N’John Doe’)`، مجبور است نوع داده `Name` را به `NVARCHAR` تبدیل کند. این تبدیل باعث می‌شود که شاخص روی `Name` غیرقابل استفاده شود، زیرا SQL Server نمی‌تواند از شاخصی که بر روی `VARCHAR` ساخته شده برای مقایسه با `NVARCHAR` بدون تبدیل ستون استفاده کند. برای جلوگیری از این مشکل، همیشه باید نوع داده‌های مقایسه‌شده را مطابقت دهید.

برای بررسی برنامه‌های اجرایی (Execution Plans) و دیدن تأثیر تبدیل‌های ضمنی، می‌توانید از دستورات زیر استفاده کنید:

DBCC FREEPROCCACHE; -- برای پاک کردن کش طرح‌های اجرایی
SET STATISTICS IO ON; -- برای مشاهده آمار I/O
-- کوئری که تبدیل ضمنی دارد
SELECT EmployeeID, Name, Salary FROM Employees WHERE Name = N'John Doe';

-- کوئری بدون تبدیل ضمنی (مقادیر تطابق دارند)
SELECT EmployeeID, Name, Salary FROM Employees WHERE Name = 'John Doe';

همچنین می‌توانید از `sys.dm_exec_query_stats` برای بررسی برنامه‌های اجرایی کش‌شده و شناسایی کوئری‌هایی با تبدیل ضمنی استفاده کنید.

**Parameter Sniffing**

Parameter Sniffing پدیده‌ای است که در آن SQL Server برای کامپایل یک برنامه اجرایی ذخیره‌شده (Stored Procedure) یا یک کوئری پارامتردار، از مقادیر اولین پارامترهایی که به آن ارسال می‌شود استفاده می‌کند. این برنامه اجرایی سپس برای فراخوانی‌های بعدی با مقادیر مختلف پارامترها، دوباره استفاده می‌شود. مشکل زمانی پیش می‌آید که مقادیر اولیه پارامترها منجر به ایجاد یک برنامه اجرایی بهینه برای آن مقادیر خاص شود، اما این برنامه برای مقادیر بعدی پارامترها که ممکن است توزیع داده‌های متفاوتی داشته باشند (مثلاً یک پارامتر که تعداد کمی ردیف را برمی‌گرداند و دیگری که تعداد بسیار زیادی ردیف را برمی‌گرداند)، بهینه نباشد.

برای مثال، فرض کنید یک جدول `Products` با تعداد زیادی محصول دارید و می‌خواهید محصولاتی را بر اساس `Color` آن‌ها جستجو کنید. برخی رنگ‌ها (مثلاً ‘Black’) ممکن است تعداد زیادی محصول داشته باشند، در حالی که رنگ‌های دیگر (مثلاً ‘Yellow’) تعداد کمی محصول.

-- ایجاد یک جدول نمونه برای نمایش Parameter Sniffing
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Color VARCHAR(50),
    Price DECIMAL(10, 2)
);

-- ایجاد شاخص روی ستون Color
CREATE NONCLUSTERED INDEX IX_Products_Color ON Products (Color);

-- درج داده (مثلاً 100000 محصول، با رنگ 'Black' برای 90% از آن‌ها و بقیه رنگ‌ها برای 10%)
-- برای سادگی، فقط چند ردیف درج می‌شود اما تصور کنید داده‌ها skewed هستند
INSERT INTO Products (ProductID, Name, Color, Price) VALUES
(1, 'Product A', 'Black', 10.00),
(2, 'Product B', 'Black', 12.50),
(3, 'Product C', 'Red', 15.00),
(4, 'Product D', 'Black', 20.00),
(5, 'Product E', 'Blue', 22.00);

-- ایجاد یک stored procedure
CREATE PROCEDURE GetProductsByColor
    @ProductColor VARCHAR(50)
AS
BEGIN
    SELECT ProductID, Name, Price
    FROM Products
    WHERE Color = @ProductColor;
END;

حالا فرض کنید ابتدا Stored Procedure را با ‘Red’ (که تعداد کمی محصول دارد) اجرا می‌کنید:

DBCC FREEPROCCACHE; -- برای اطمینان از پاک شدن برنامه اجرایی قبلی
SET STATISTICS IO ON;
EXEC GetProductsByColor @ProductColor = 'Red';

SQL Server یک برنامه اجرایی را کامپایل می‌کند که برای تعداد کم ردیف‌های ‘Red’ بهینه است (احتمالاً یک Index Seek). سپس، اگر بلافاصله آن را با ‘Black’ (که تعداد زیادی محصول دارد) اجرا کنید:

SET STATISTICS IO ON;
EXEC GetProductsByColor @ProductColor = 'Black';

SQL Server از همان برنامه اجرایی که برای ‘Red’ کامپایل شده بود استفاده می‌کند، که ممکن است برای ‘Black’ ناکارآمد باشد (مثلاً به جای Index Seek، Index Scan یا Table Scan بهتر باشد).

برای حل مشکل Parameter Sniffing، چندین راهکار وجود دارد:

1. **`WITH RECOMPILE`**: این گزینه Stored Procedure را مجبور می‌کند تا هر بار که فراخوانی می‌شود، دوباره کامپایل شود. این کار همیشه جدیدترین آمار را در نظر می‌گیرد اما سربار CPU را افزایش می‌دهد.

    CREATE PROCEDURE GetProductsByColor_Recompile
        @ProductColor VARCHAR(50)
    AS
    BEGIN
        SELECT ProductID, Name, Price
        FROM Products
        WHERE Color = @ProductColor
        OPTION (RECOMPILE); -- این یک Query Hint است
    END;
    

یا می‌توانید `WITH RECOMPILE` را در هنگام ایجاد Stored Procedure استفاده کنید:

    ALTER PROCEDURE GetProductsByColor
        @ProductColor VARCHAR(50)
    WITH RECOMPILE
    AS
    BEGIN
        SELECT ProductID, Name, Price
        FROM Products
        WHERE Color = @ProductColor;
    END;
    

2. **`OPTIMIZE FOR UNKNOWN`**: این گزینه SQL Server را مجبور می‌کند تا پارامتر را نادیده بگیرد و به جای آن از میانگین توزیع آماری برای کامپایل برنامه اجرایی استفاده کند. این کار می‌تواند برای داده‌های با توزیع نامتوازن مفید باشد.

    CREATE PROCEDURE GetProductsByColor_OptimizeForUnknown
        @ProductColor VARCHAR(50)
    AS
    BEGIN
        SELECT ProductID, Name, Price
        FROM Products
        WHERE Color = @ProductColor
        OPTION (OPTIMIZE FOR (@ProductColor UNKNOWN));
    END;
    

3. **`OPTIMIZE FOR VALUE(…)`**: این گزینه به شما اجازه می‌دهد تا SQL Server را وادار کنید که برنامه اجرایی را برای یک مقدار پارامتر خاص کامپایل کند، حتی اگر آن مقدار در واقع ارسال نشده باشد.

    CREATE PROCEDURE GetProductsByColor_OptimizeForValue
        @ProductColor VARCHAR(50)
    AS
    BEGIN
        SELECT ProductID, Name, Price
        FROM Products
        WHERE Color = @ProductColor
        OPTION (OPTIMIZE FOR (@ProductColor = 'DefaultColorThatIsAverage'));
    END;
    

4. **استفاده از متغیرهای محلی**: می‌توانید مقدار پارامتر ورودی را به یک متغیر محلی اختصاص دهید. این کار باعث می‌شود SQL Server نتواند مقدار پارامتر را “بو” کند و به جای آن از آمار کلی استفاده کند.

    CREATE PROCEDURE GetProductsByColor_LocalVariable
        @ProductColor VARCHAR(50)
    AS
    BEGIN
        DECLARE @LocalColor VARCHAR(50) = @ProductColor;
        SELECT ProductID, Name, Price
        FROM Products
        WHERE Color = @LocalColor;
    END;
    

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