مشکل Overestimation در SQL Server

مقدمه

هنگامی که ایندکس به ازای شرط کوئری شما وجود دارد و Statistics در تخمین تعداد رکوردهای بازگشتی به SQL Server کمک می نماید، چگونه ممکن است SQL Server تعداد رکوردهای بازگشتی را به شکل عجیبی اشتباه تخمین بزند؟ Statistics بروز رسانی شده، اما مشکل مطرح شده وجود دارد!

جهت نمایش مشکل مطرح شده کوئری زیر را بر روی Stack Overflow Database اجرا می نماییم

SELECT *
FROM dbo.Users
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName

در کوئری فوق از SQL Server خواسته شده است، کاربرانی را نمایش دهد، که تاریخ ثبت نام آن ها، بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’ باشد و Reputation بیشتر از ۱۰۰ داشته باشند.
SQL Server از طریق Statistics می داند که، کاربرانی زیادی بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’  ثبت نام نموده اند و نیز می داند که کاربرانی زیادی در کل جدول کاربران Reputation بیشتر از ۱۰۰ دارند.
SQL Server این اطلاعات را به صورت جدا از هم دارد و در واقع Overlap بین دو شرط کوئری را نمی تواند تشخیص دهد.
یعنی SQL Server نمی داند که چه تعدادی از کاربران Reputation بیشتر از ۱۰۰ دارند و بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’ ثبت نام نموده اند.
در ادامه ایندکس هایی مناسب برای استفاده کوئری مورد بحث، بر روی جدول Users ایجاد می نمائیم:

CREATE INDEX CreationDate_Reputation
  ON dbo.Users(CreationDate, Reputation);
CREATE INDEX Reputation_CreationDate
  ON dbo.Users(Reputation, CreationDate);
GO

 ما ایندکس های ترکیبی شامل هر دو ستونی که در شرط کوئری آمده اند، بر روی جدول Users ایجاد نمودیم.
تصویر زیر Plan اجرای کوئری را نمایش می دهد:همان طور که در Plan اجرای کوئری مشاهده می شود SQL Server از ایندکس های ایجاد شده استفاده نکرد و عمل Clustered Index Scan را انجام داد.
آیا SQL Server اشتباه نمود؟ بله، قطعا اشتباه نمود. SQL Server تخمین زد که کوئری ۴۲۴۲۵ رکورد را برمی گرداند در صورتی که تعداد رکورد های واقعی بازگشتی کوئری ۴۱۹ رکورد است.
این همان مشکل Overestimation می باشد.
برای اثبات بهینه نبودن Plan اجرایی که SQL Server انتخاب نمود، کوئری را Force می نمائیم که از ایندکس ایجاد شده استفاده نماید:

SELECT *
FROM dbo.Users WITH (INDEX = CreationDate_Reputation)
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName;

تصویر زیر Plan اجرایی کوئری را نشان می دهدمشاهده می نمائید که Plan اجرای کوئری به صورت NonClustered Index Seek می باشد.
تصویر زیر IO دو کوئری را مقایسه می نمایدکوئری اول ۱۴۲ هزار Logical IO و کوئری دوم هزار و هفتصد Logical IO دارد.
استفاده از Hint Index در کوئری راه حل مناسبی نمی باشد، همچنین در این مقاله ما قصد نداریم به بررسی Tipping Point بپردازیم. (این موضوع ها نیاز به مقاله های جداگانه دارند.)
در این مثال ما می خواهیم با استفاده از بازنویسی کوئری مشکل Overestimation را اصطلاحا دور بزنیم. زیرا که Overestimation همچنان وجود خواهد داشت اما کوئری IO بالایی را بر سیستم تحمیل نمی کند و Plan اجرای کوئری به صورت Index Seek خواهد بود. (در مثال مورد بحث ما Index Seek بسیار بهینه تر از Index Scan است).
توجه داشته باشیم که کلید کلاستر ایندکس جدول Users بر روی ستون ID می باشد که Primary Key جدول نیز هست و کلید کلاستر ایندکس Built-In در دل NonClustered Index ها وجود دارد.
کوئری مورد بحث را به شکل زیر بازنویسی می نمائیم:

; WITH CTE AS (
SELECT Id
FROM dbo.Users
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
)
SELECT u.* FROM dbo.Users u
INNER JOIN CTE c
 ON c.Id = u.Id
ORDER BY DisplayName

تصویر زیر تعداد Logical IO مربوط به کوئری را بعد از بازنویسی نمایش می دهد که برابر با ۱۷۷۲ می باشد:
همچنین Actual Execution Plan کوئری بعد از بازنویسی در تصویر زیر نمایش داده شده است

نتیجه گیری

ایندکس هایی که بر روی جداول ایجاد می شوند لزوما توسط SQL Server مورد استفاده قرار نمی گیرند حتما باید آن ها را مانیتور نمائیم. همچنین موارد زیادی وجود دارد که Performance کوئری ها از طریق بازنویسی کوئری افزایش می یابد.