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

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

نوشته شده توسط: تورج عزیزی
۱۲ تیر ۱۳۹۵
زمان مطالعه: 10 دقیقه
۰
(۰)

مقدمه

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

پیش نیازهای مقاله

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

دوره Performance Tuning در SQL Server

 اما 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 یا عادی بروز شود.
امیدوارم مفید واقع شده باشد.

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
معرفی نویسنده
تورج عزیزی
مقالات
17 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    •    سلام

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

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

    • سلام

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

    •    با سلام و تشکر از مقاله خوبتون
      من منظورتونو از density vector متوجه نشدم میشه کمی بیشتر توضیح بدید؟

    • بسیار عالی. دنبال این مقاله میگشتم.
      مشکل من مرتفع شد  

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
جشنواره عیدآموز نیک آموز، سال جدید رو با قدرت شروع کن
مشاهده تخفیف ها
close-image