در این مقاله تاثیر 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) |
و مهمتر اینکه Query Optimizer فقط وقتی همان شرط بالا در یک کوئری بیاید از این شیء آمار و در غیر این صورت از آمار پیش فرض استفاده می کند:
[sql] SELECT SalesAmount FROM Country |
آزمایش تاثیر Filtered Statistics بر Performance:
کد های زیر را برای تولید دیتای آزمایشی اجرا کنید:
[sql] — Create a new database — Use it — Create a new table — Create a new table |
ساخت ایندکس:
[sql] — Create a Non-Clustered Index — Create a Clustered Index |
درج دیتا:
[sql] — Insert a few records into the Lookup Table — Insert uneven distributed order data DECLARE @i INT = 1 WHILE @i <= 1000 |
حصول اطمینان از بروز بودن آمار:
[sql] — Update the Statistics on both tables |
کوئری تستی:
[sql] SELECT SalesAmount FROM Country |
وقتی به پلن اجرا نگاه می کنیم متوجه یک مشکل بزرگ در تخمین 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_Austria ON Country(ID) |
با اجرای مجدد کوئری :


با ایجاد Filtered Statistics همه چیز تمام نمی شود. باید حواستان به نگهداری از آمار باشد چون SQL Server با تغییر 20% از دیتای شما آمار را بروز می کند. یعنی اگر جدولی با 10000 رکورد داشته باشید باید 2000 تغییر در جدول اتفاق بیفتد تا آمار شما اعم از Filtered Statistics یا عادی بروز شود.
امیدوارم مفید واقع شده باشد.
5 دیدگاه
محمد عباسی
بسیار عالی. دنبال این مقاله میگشتم.
مشکل من مرتفع شد
seyedmahdi
با سلام و تشکر از مقاله خوبتون
من منظورتونو از density vector متوجه نشدم میشه کمی بیشتر توضیح بدید؟
تورج عزیزی
سلام
توی مقالات پیش نیاز توضیح دادم
منظور selectivity اون ستونی که آمار به ازای اون ساخته شده است که به شکل 1 تقسیم بر تعداد مقادیر یونیک تعریف میشه و Optimizer با این مقدار می تونه بفهمه که چند درصد از رکوردهای جدول یک شرط با مقدار بخصوص (مقداری از مقادیر این ستون) رو ارضا می کنه.
Hope this helps.
فرشید علی اکبری
سلام
مصطفی زماني
سلام
با تشکر از مقاله سودمند شما
به نظر میرسد بهروز نگه داشتن آمار کار مشکلی است. به ویژه در شرایطی که تعداد رکوردهای جدول بالا باشد و تغییرات پارامتر ورودی نیز متعدد است.