بیایید این کوئری را Tune (بهینه) کنیم!

دوستان در این مقاله یک کوئری را به عنوان نمونه انتخاب کرده ام و قصد دارم قدم به قدم آن را tune کرده و تاثیر tune را پس اعمال آن بررسی کنم.

DECLARE @i INT = 999
SELECT
SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID < @i
ORDERBY CarrierTrackingNumber
GO

همانطوری که در اینجا می بینید من از یک متغیر محلی به همراه یک گزاره شرطی نامساوی استفاده کرده ام تا تعدادی ردیف را از جدول Sales.SalesOrderDetail برگردانم. وقتی کوئری را اجرا می کنید و به پلن اجرای کوئری نگاه می کنید با چند مشکل جدی در پلن مواجه می شوید.

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
GO

بعد از ایجاد Non-clustered index باید تغییرات داده شده را آزمایش کنیم، بنابراین کوئری را دوباره اجرا می کنیم. و حدس می زنید چه اتفاقی می افتد؟ Query Optimizer از Non-clustered index استفاده نمی کند! ما میتوانیم Query Optimizer را مجبور کنیم از Non-clustered index استفاده کند تا بهتر بفهمیم *چرا* Query Optimizer ایندکس ما را انتخاب نمی کند:

DECLARE @i INT = 999
SELECT
SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
LineTotal
FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
WHERE ProductID < @i
ORDER BY CarrierTrackingNumber
GO

وقتی به پلن اجرا نگاه می کنید، یک جانور می بینیم – یک پلن موازی!

• CarrierTrackingNumber
• OrderQty
• UnitPrice
• UnitDiscountPrice

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
WITH
(
DROP_EXISTING = ON
)
GO

ما یک تغییر دیگر اعمال کرده ایم، بنابراین باید تغییرمان را دوباره آزمایش کنیم. اما این بار بدون query hint ، چون Query Optimizer  باید Non-clustered index  را به طور خودکار استفاده کند. و حدس بزنید چه اتفاقی می افتد؟ حالا ایندکس انتخاب شده و در پلن اجرا قابل مشاهده است:

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
WITH
(
DROP_EXISTING = ON
)
GO

می توانید در تعریف ایندکس ببینید که ما حالا دیتا را بر حسب ستون های ProductID و CarrierTrackingNumber از پیش مرتب کرده داریم. وقتی کوئری را دوباره اجرا کنید و به پلن اجرا نگاه کنید می توانید ببینید که  عملگر Sort حذف شده و SQL Sevrer سطح برگ Non-clustered index  را با یک شرط باقیمانده (residual predicate) به طور کامل اسکن می کند:

CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?]
ON [Sales].[SalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])
GO

وقتی کوئری اولیه را دوباره اجرا می کنید چیز های شگفت انگیزی می بینید: Query Optimizer از Non-clustered index قبلی *ما* استفاده کرده و Missing Index Recommendations را کنار گذاشته! شما ایندکسی ایجاد کرده اید هیچوقت توسط SQL Server استفاده نمی شود مگر برای عملیات INSERT, UPDATE یا DELETE که در آن بایدNon-clustered indexهم تاثیر بگیرد.