حل مشکل تخمین Cardinality با استفاده از Filtered Statistics

در این مقاله تاثیر Filtered Statistics را بر روی Performance خواهید دید.

پیش نیازهای مقاله:
معرفی Statistics در SQL Server
statistic ها در چه شرایطی توسط SQL Server به طور خودکار به روز می شوند

از SQL Server 2008 به بعد، Filtered Statistics به عنوان یک قابلیت اضافه شد. با استفاده از Filtered Statistics می توان روی زیر مجموعه ای از دیتای کلی یک جدول شیء آمار ساخت.
Filtered Statistics به این دلیل می تواند خیلی موثر واقع شود: قسمت Histogram در Statistics محدودیت ۲۰۰ طبقه ای را دارد و این به خاطر این است که اطلاعات شیء آمار باید در حداکثر یک Page جا شود.

 
اما Filtered Statistics این امکان را فراهم می کند که بتوان روی زیر مجوعه ای از دیتای یک جدول آمار ساخت و البته چون رکوردهای کمتری نسبته به کل جدول داریم آمار دقیق تری خواهیم داشت. و در نتیجه تخمین Cardinality توسط Query Optimizer بهتر انجام می شود به این معنی که پلن بهتری خواهیم داشت و در نهایت سرعت بهتری!

مثال

 CREATE STATISTICS Country_Austria ON Country(ID)
WHERE Name = 'Austria'
GO 

و مهمتر اینکه Query Optimizer فقط وقتی همان شرط بالا در یک کوئری بیاید از این شیء آمار  و در غیر این صورت از آمار پیش فرض استفاده می کند:

 SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'Austria'
GO 

آزمایش تاثیر Filtered Statistics بر Performance:

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

 -- Create a new database
CREATE DATABASE FilteredStatistics
GO

-- Use it
USE FilteredStatistics
GO

-- Create a new table
CREATE TABLE Country
(
ID INT PRIMARY KEY,
Name VARCHAR(100)
)
GO

-- Create a new table
CREATE TABLE Orders
(
ID INT,
SalesAmount DECIMAL(18, 2)
)
GO 

ساخت ایندکس:

 -- Create a Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Name ON Country(Name)
GO

-- Create a Clustered Index
CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount)
GO 

درج دیتا:

 -- Insert a few records into the Lookup Table
INSERT INTO Country VALUES(0, 'Austria')
INSERT INTO Country VALUES(1, 'UK')
INSERT INTO Country VALUES(2, 'France')
GO

-- Insert uneven distributed order data
INSERT INTO Orders VALUES(0, 0)

DECLARE @i INT = 1 

WHILE @i <= 1000
BEGIN
INSERT INTO Orders VALUES (1, @i)
SET @i += 1
END
GO 

حصول اطمینان از بروز بودن آمار:

 -- Update the Statistics on both tables
UPDATE STATISTICS Country WITH FULLSCAN
UPDATE STATISTICS Orders WITH FULLSCAN
GO 

کوئری تستی:

 SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = 'UK'
OPTION
(
RECOMPILE,-- Used to see the Statistics Output
    QUERYTRACEON 3604,-- Redirects the output to SSMS
    QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
)
GO 

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

تخمین SQL Server به تعداد ۵۰۱ ردیف است و در واقعیت ۱۰۰۰ ردیف برگشت داده شده است.
SQL Server  با مراجعه به Density Vector آمار ایندکس  idx_ID_SalesAmount محاسبه را انجام داده: چون فقط ۲ مقدار یونیک داریم پس Density Vector برابر با ۰٫۵ می شود و ۱۰۰۱ * ۰٫۵ = ۵۰۱٫

وقتی همان کوئری را به شرط Austria اجرا کنیم، باز هم تخمین برابر ۵۰۱ است و کوئری ۱ رکورد بر می گرداند. این تخمین اشتباه تاثیر بدی در پلن اجرا دارد چون درخواست حافظه برای اجرا توسط هر عملگر در پلن اجرا و انتخاب نوع عملگر مستقیماً با توجه به تعداد ورودی های آن عملگر صورت می گیرد.
در چنین سناریوی خاصی می توانید با Filtered Statistics به تخمین کمک کنید:

 -- Fix the problem by creating Filtered Statistics Objects
CREATE STATISTICS Country_UK ON Country(ID)
WHERE Name = 'UK'

CREATE STATISTICS Country_Austria ON Country(ID)
WHERE Name = 'Austria'
GO 

با اجرای مجدد کوئری :

با ایجاد Filtered Statistics همه چیز تمام نمی شود. باید حواستان به نگهداری از آمار باشد چون SQL Server با تغییر ۲۰% از دیتای شما آمار را بروز می کند.  یعنی اگر جدولی با ۱۰۰۰۰ رکورد داشته باشید باید ۲۰۰۰ تغییر در جدول اتفاق بیفتد تا آمار شما اعم از Filtered Statistics یا عادی بروز شود.

امیدوارم مفید واقع شده باشد.