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

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

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

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

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

مثال

[sql] CREATE STATISTICS Country_Austria ON Country(ID)
WHERE Name = ‘Austria’
GO [/sql]

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

[sql] SELECT SalesAmount FROM Country
INNER JOIN Orders ON Country.ID = Orders.ID
WHERE Name = ‘Austria’
GO [/sql]

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

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

[sql] — 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 [/sql]

ساخت ایندکس:

[sql] — 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 [/sql]

درج دیتا:

[sql] — 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 [/sql]

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

[sql] — Update the Statistics on both tables
UPDATE STATISTICS Country WITH FULLSCAN
UPDATE STATISTICS Orders WITH FULLSCAN
GO [/sql]

کوئری تستی:

[sql] 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 [/sql]

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

تخمین SQL Server به تعداد 501 ردیف است و در واقعیت 1000 ردیف برگشت داده شده است.
SQL Server با مراجعه به Density Vector آمار ایندکس idx_ID_SalesAmount محاسبه را انجام داده: چون فقط 2 مقدار یونیک داریم پس Density Vector برابر با 0.5 می شود و 1001 * 0.5 = 501.

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

[sql] — 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 [/sql]

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

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

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

درباره نویسنده

تورج عزیزی

مشاوره، تحقیقات و انجام پروژه در زمینه SQL Server Performance Tuning

5 دیدگاه

محمد عباسی
تیر ۱۳, ۱۳۹۵ در ۱۳:۱۶
بسیار عالی. دنبال این مقاله میگشتم.
مشکل من مرتفع شد

پاسخ
seyedmahdi
تیر ۱۴, ۱۳۹۵ در ۱۱:۵۴
با سلام و تشکر از مقاله خوبتون
من منظورتونو از density vector متوجه نشدم میشه کمی بیشتر توضیح بدید؟

پاسخ
تورج عزیزی
تیر ۱۵, ۱۳۹۵ در ۱۹:۵۹
سلام
توی مقالات پیش نیاز توضیح دادم
منظور selectivity اون ستونی که آمار به ازای اون ساخته شده است که به شکل 1 تقسیم بر تعداد مقادیر یونیک تعریف میشه و Optimizer با این مقدار می تونه بفهمه که چند درصد از رکوردهای جدول یک شرط با مقدار بخصوص (مقداری از مقادیر این ستون) رو ارضا می کنه.
Hope this helps.

پاسخ
فرشید علی اکبری
تیر ۲۱, ۱۳۹۵ در ۱۳:۰۸
سلام

مطلب بسیار مفید وکاربردی خوبی بود.

تشکر.

پاسخ
مصطفي زماني
تیر ۲۴, ۱۳۹۵ در ۰۹:۲۳
سلام

با تشکر از مقاله سودمند شما

به نظر می‌رسد به‌روز نگه داشتن آمار کار مشکلی است. به ویژه در شرایطی که تعداد رکوردهای جدول بالا باشد و تغییرات پارامتر ورودی نیز متعدد است.

پاسخ

درباره نویسنده

تورج عزیزی

5 دیدگاه

محمد عباسی

seyedmahdi

تورج عزیزی

فرشید علی اکبری

مصطفي زماني

ارسال یک نظر لغو پاسخ

دوره‌های در حال ثبت نام و اجرا

خدمات حرفه‌ای نیک آموز

استخدام برنامه نویس

پُرفروش‌ترین محصولات

اینماد سایت