معرفی Statistics در SQL Server

معرفی Statistics در SQL Server، در مقاله قبلی با عنوان Statistic ها چه زمانی توسط SQL Server به طور خودکار بروز می شوند پیش نیاز آشنایی با Statistic ها را عنوان کردیم. در این مقاله قصد دارم تا به معرفی این پیش نیاز بپردازم. قبل از اینکه وارد این بحث بشوم باید با یکی از مراحل اجرای کوئری به نام Query Optimization آشنا شویم. این مرحله توسط کامپوننتی به نام Query Optimizer انجام می شود. وظیفه QO تولید یک پلن اجرای تخمینی است. تولید پلن اجرای تخمینی همانطور که در مقاله قبلی گفته شد با تکیه بر Statistic ها انجام می شود. برای درک بهتر مفاهیم آموزش جامع SQL Server را مطالعه کنید.

 

یک پلن اجرا مراحلی را که Storage Engine باید دنبال کند تا به Result Set نهایی برسد را تعیین می کند. QO ورودی خود را از کامپوننتی به نام Algebrizer دریافت می کند این ورودی query processor tree نامیده می شود. می توانید در مورد query processor tree به عنوان یک ساختمان داده که مراحل اجرای منطقی کوئری درخواستی را به فرم یک درخت نشان می دهد فکر کنید.منظور از اجرای منطقی آن است که روش اجرای فیزیکی مشخص نشده فقط خود عمل درخواستی مشخص شده است.

QO با استفاده از query processor tree  و آماری که در مورد توزیع داده ها در اختیار دارد (همان اشیای Statistic) سعی می کند با استفاده از روش هایی بهترین روش پیاده سازی query processor tree را  در کمترین زمان بیابد که از آن به Good Enough Plan یاد می شود. QO پلن های متعددی را تولید کرده و هزینه هر یک از پلن ها را تخمین زده و ارزان ترین پلن را انتخاب می کند. هر پلن از تعدادی عملگر تشکیل شده و هر عملگر بیانگر یک عمل فیزیکی است مانند اسکن جدول.
بدیهی است تعداد رکوردهایی که هر عملگر پردازش می کند با میزان هزینه اختصاص یافته به آن عملگر رابطه مستقیم دارد. همچنین دانستن تعداد رکوردهای ورودی عملگر،در انتخاب الگوریتم اجرای عملگر فیزیکی تاثیر مستقیم دارد. مثلاٌ برای عمل JOIN که که یک عمل منطقی است باید یک معادل فیزیکی پیدا شود که همان الگوریتم پیاده سازی این عمل است که از بین سه الگوریتم NESTED LOOPS و HASH و MERGE بسته به بزرگی دو ورودی و خصایصی مانند وجود ایندکس انتخاب می شود. مثلاً برای انتخاب الگوریتم MERGE هر دو ورودی حتماً باید بر اساس ستون Join مرتب باشند و به اندازه کافی بزرگ باشند. پیشنهاد میکنیم برای درک بهتر مفاهیم دوره کوئری نویسی پیشرفته را مطالعه کنید.

تخمین تعداد رکوردهای ورودی یک عملگر فیزیکی توسط اشیای آمار انجام می شود. به عمل محاسبه تخمبن تعداد رکوردهای ورودی یک عملگر Cardinality Estimation گفته می شود. اجازه دهید چگونگی محاسبه تخمین را پس از معرفی اشیای آمار توضیح دهم. Statistic به دو روش دستی و خودکار تولید می شوند. Statistic از دو بخش تشکیل شده: Density و Data Distribution. درک مفهوم Density ساده تر است. به سادگی نشان می دهد که چه تعداد مقدار یونیک در یک یا بیش از یک ستون وجود دارد. فرمول محاسبه آن هم بسیار ساده است:

SELECT 1.0 / COUNT(DISTINCT MyColumn) FROM dbo.MyTable;

Density از آن جهت اهمیت دارد که Selectivity یک ایندکس را مشخص می کند. منظور از Selectivity درصدی از رکوردهاست که شرطی خاص را ارضا می کنند. هر چه این مقدار کوچکتر باشد Selectivity آن بالاتر و چگالی یا Density آن کمتر است و بالعکس. هر چه Selectivity بالاتر باشد احتمال انتخاب ایندکس توسط Optimizer بالاتر می شود. 

توزیع داده ها تحلیلی آماری از داده های موجود در ستون اول آمار ارائه می دهد. حتی در مورد یک ایندکس ترکیبی هم فقط آمار ستون اول آن تولید می شود. به این دلیل است که گفته می شود ستونی که تعداد مقادیر یونیک در آن بیشتر است (پس Selectivity آن هم بالاتر است)  به عنوان ستون اول انتخاب شود (Leading Column). نمایش این توزیع توسط تابع Histogram انجام می شود این تابع تعداد مقادیری از داده ها را که در یک رنج خاص که Step نامیده می شود قرار می گیرند را شمارش می کند. تخمین تعداد رکوردها با استفاده از این Step ها انجام می شود.

• RANGE_HI_KEY: مقدار سقف Step جاری.
• RANGE_ROWS: نشان دهنده تعداد ردیف هایی است که مقداری بالاتر از مقدار سقف طبقه قبلی و کمتر از مقدار سقف طبقه فعلی دارند.
• EQ_ROWS: تعداد رکوردهایی که مقدار ستون اول آمار برابر با مقدار سقف طبقه جاری.
• DISTINCT_RANGE_ROWS: تعداد مقادیر یونیک در این Step.
• AVG_RANGE_ROWS: میانگین تعداد تکرار هر مقدار در این Step.

دادهای این بخش به دو روش جمع آوری می شوند: به شکل نمونه برداری شده (Sampled) و یا Full Scan. وقتی ایندکس ها ایجاد یا Rebuild می شوند به طور خودکار از روش  Full Scan برای تولید داده های این بخش استفاده می شود. وقتی آمار به طور خودکار به روز می شود SQL Server از روش Sampled برای تولید داده های آماری استفاده می کند. روش Sampled تولید و بروز رسانی آمار را به سرعت انجام می دهد و در عین حال هم می تواند از دقت کمتری نسبت به روش Full Scan برخوردار باشد چون رکوردها را به طور تصادفی خوانده می شود اما همه رکورد ها در نظر گرفته نمی شوند. می توان به روش دستی آمار را ایجاد یا به روش Full Scan بروز کرد.

بروز کردن آمار به روش دستی یا استفاده از روال sp_updatestats  برای بروزرسانی همه آمار یک دیتابیس یا با دستور UPDATE STATISTICS برای بروز رسانی آماری خاص انجام شود. برای نمایش وضعیت آمار هم می توان از دستور DBCC SHOW_STATISTICS استفاده کرد. برای نمایش محتویات آمار ایندکس IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode روی جدول Person.Address دیتابیس AdventureWorks از دستور SHOW_STATISTICS می توان استفاده کرد:

DBCC SHOW_STATISTICS ("Person.Address",
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode);

منظور از مقدار ستون Updated آخرین زمانی است که آمار بروز شده است. Rows Sampled تعداد ردیف هایی است که برای ساخت آمار نمونه برداری شده و اگر با Rows اختلاف فاحشی داشته باشد نشان از جمع آوری یا به روز شدن آمار با استفاده از روش Sampled دارد. 

اطلاعات بخش Density

در ستون All Density مقدار ردیف اول میزان Selecivity ستون AddressLine1 را نشان می دهد که معادل ۰.۰۰۰۰۷۳۷۰۸۲۶ است و میبینید که به ۰ بسیار نزدیک است و نشان می دهد تعداد مقادیر یونیک در این ستون نسبتاً بالاست و می توان با تقسیم ۱ بر  ۰.۰۰۰۰۷۳۷۰۸۲۶ تعداد مقادیر یونیک را بدست آورد (۱۳۵۶۷ مقدار یونیک).

در ردیف دوم هم چگالی ترکیب مقادیر AddressLine1 و AddressLine2 نشان داده شده و همانطور که می بینید  All Density کمتر و کمتر شده است (چون با افزایش تعداد ستون ها احتمال یونیک بودن مقادیر ترکیبی هم افزایش می یابد). 

در شکل بالا هم چگونگی توزیع داده ها را میبینید. وجود اعشار هم در ستون RANGE_ROWS علامتی از حدودی بودن این مقادیر است که نتیجه روش Sampled است. اعداد ستون RANGE_ROWS در شکل بالا صحیح بوده که نشان از تولید این اطلاعات به روش Full است.

کوئری زیر را در نظر بگیرید:

select * from Person.Address where AddressLine1 = '8713 Yosemite Ct.'

در بخش هیستوگرام ابتدا باید Step ای که مقدار مورد جستجو در آن قرار دارد یاقت شود، که ردیفی که با آدرس ۸۷۱۳ شروع می شود Step مورد نظر است. مقدار مورد جستجو در این Range قرار دارد و تعداد رکوردهایی که مقدار AddressLine1 آنها در این رنج قرار می گیرد به طور متوسط برابر ۱.۳۹۷۲۶ است یعنی مقدار ستون AVG_RANGE_ROWS. اگر مقدار شرط WHERE برابر ‘۸۷۳۸ Crawford Street’ باشد تعداد رکورد تخمینی توسط Optimizer دقیقاً ۲ خواهد بود چون یکی از Step ها دارای مقدار سقف ‘۸۷۳۸ Crawford Street’ است و مقدار EQ_ROWS آن دقیقاً ۲ است.

وقتی شما در یک SP در شرط WHERE به از یک متغیر محلی استفاده میکنید چون مقدار متغیر در زمان کامپایل کوئری معلوم نیست (و تنها در زمان اجرا مشخص می شود) Optimizer مجبور به استفاده از ستون AVG_RANGE_ROWS است و تخمین ما یک تخمین دقیق نخواهد بود و پلن اجرای تولید شده یک پلن SubOptimal خواهد بود. به همین دلیل است که استفاده از متغیر محلی در SP مشکل ساز است. این کار مانند استفاده از یک Hint به نام )OPTIMIZE (FOR UNKNOWN در انتهای کوئری است.

DECLARE @StateProvinceID int = 80
SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID;

 

معرفی Statistics در SQL Server، بروز نبودن آمار باعث انتخاب نادرست الگوریتم های اجرا در پلن اجرا و همچنین درخواست حافظه کمتر از حد نیاز برای اجرای کوئری می شود که هر یک مشکلات خاص خودشان را به همراه دارند مانند استفاده مفرط از دیتابیس TempDB در هنگامی که RAM کافی در سیستم وجود دارد که به آن Spill to disk هم گفته می شود و به شکل یک آیکون هشدار روی عملگر فیزیکی نشان داده می شود و یا انتخاب HASH JOIN در شرایطی که NESTED LOOPS میتوانسته انتخاب بهتری باشد. 

 کامپوننت Cardinality Estimator به عنوان بخشی از Query Optimizer که وظیفه تخمین را بر عهده دارد از نسخه ۷.۰ تا ۲۰۱۲ تغییری نکرد اما در ۲۰۱۴ تغییراتی در آن اعمال شده (به عنوان مثال استفاده از الگوریتمی به نام Exponential Backoff که یک تغییر بزرگ محسوب می شود) که در فرصت های بعدی به این موضوع نیز می پردازم. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.