خانه SQL Server معرفی Statistics در SQL Server SQL Server افزایش سرعت SQL Server نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۲۹ مرداد ۱۳۹۴ آخرین بروزرسانی: 05 شهریور 1402 زمان مطالعه: 14 دقیقه ۵ (۱) مقدمه در مقاله قبلی با عنوان Statistic ها چه زمانی توسط SQL Server به طور خودکار بروز می شوند پیش نیاز آشنایی با Statistic ها را عنوان کردیم. در این مقاله قصد دارم تا به معرفی این پیش نیاز بپردازم. قبل از اینکه وارد این بحث بشوم باید با یکی از مراحل اجرای کوئری به نام Query Optimization آشنا شویم. این مرحله توسط کامپوننتی به نام Query Optimizer انجام می شود. وظیفه QO تولید یک پلن اجرای تخمینی است. تولید پلن اجرای تخمینی همانطور که در مقاله قبلی گفته شد با تکیه بر Statistic ها انجام می شود. یک پلن اجرا مراحلی را که 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 ساده تر است. به سادگی نشان می دهد که چه تعداد مقدار یونیک در یک یا بیش از یک ستون وجود دارد. فرمول محاسبه آن هم بسیار ساده است: Density = 1 / Number of distinct values for column(s) خودتان هم می توانید از صحت مقدار مطمئن شوید: SELECT 1.0 / COUNT(DISTINCT MyColumn) FROM dbo.MyTable; Density از آن جهت اهمیت دارد که Selectivity یک ایندکس را مشخص می کند. منظور از Selectivity درصدی از رکوردهاست که شرطی خاص را ارضا می کنند. هر چه این مقدار کوچکتر باشد Selectivity آن بالاتر و چگالی یا Density آن کمتر است و بالعکس. هر چه Selectivity بالاتر باشد احتمال انتخاب ایندکس توسط Optimizer بالاتر می شود. Data Distribution توزیع داده ها تحلیلی آماری از داده های موجود در ستون اول آمار ارائه می دهد. حتی در مورد یک ایندکس ترکیبی هم فقط آمار ستون اول آن تولید می شود. به این دلیل است که گفته می شود ستونی که تعداد مقادیر یونیک در آن بیشتر است (پس Selectivity آن هم بالاتر است) به عنوان ستون اول انتخاب شود (Leading Column). نمایش این توزیع توسط تابع Histogram انجام می شود این تابع تعداد مقادیری از داده ها را که در یک رنج خاص که Step نامیده می شود قرار می گیرند را شمارش می کند. تخمین تعداد رکوردها با استفاده از این Step ها انجام می شود. حداکثر ۲۰۰ Step در SQL Server در یک شیء آمار وجود دارد. هر 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 کمتر و کمتر شده است (چون با افزایش تعداد ستون ها احتمال یونیک بودن مقادیر ترکیبی هم افزایش می یابد). اطلاعات بخش Data Distribution در شکل بالا هم چگونگی توزیع داده ها را میبینید. وجود اعشار هم در ستون RANGE_ROWS علامتی از حدودی بودن این مقادیر است که نتیجه روش Sampled است. اعداد ستون RANGE_ROWS در شکل بالا صحیح بوده که نشان از تولید این اطلاعات به روش Full است. چگونگی محاسبه تخمین Cardinality توسط Query Optimizer کوئری زیر را در نظر بگیرید: select * from Person.Address where AddressLine1 = '8713 Yosemite Ct.' پلن اجرای تخمینی این کوئری در شکل زیر آمده است: Query Optimizer برای تخمین تعداد ردیف ها به این شکل عمل می کند در بخش هیستوگرام ابتدا باید 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; بروز نبودن آمار باعث انتخاب نادرست الگوریتم های اجرا در پلن اجرا و همچنین درخواست حافظه کمتر از حد نیاز برای اجرای کوئری می شود که هر یک مشکلات خاص خودشان را به همراه دارند مانند استفاده مفرط از دیتابیس TempDB در هنگامی که RAM کافی در سیستم وجود دارد که به آن Spill to disk هم گفته می شود و به شکل یک آیکون هشدار روی عملگر فیزیکی نشان داده می شود و یا انتخاب HASH JOIN در شرایطی که NESTED LOOPS میتوانسته انتخاب بهتری باشد. کامپوننت Cardinality Estimator به عنوان بخشی از Query Optimizer که وظیفه تخمین را بر عهده دارد از نسخه ۷.۰ تا ۲۰۱۲ تغییری نکرد اما در ۲۰۱۴ تغییراتی در آن اعمال شده (به عنوان مثال استفاده از الگوریتمی به نام Exponential Backoff که یک تغییر بزرگ محسوب می شود) که در فرصت های بعدی به این موضوع نیز می پردازم. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول مسعود طاهری دوره آموزشی نگهداری از بانکهای اطلاعاتی در SQL Server 1.180.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مهران رحمتی ۰۲ / ۰۶ / ۹۴ - ۰۴:۴۹ سلام. بابت زحمات شما ممنون پاسخ به دیدگاه محمّدامین مزیدی ۰۲ / ۰۶ / ۹۴ - ۰۸:۴۳ خیلی عالی بود یه روز صبح می خواستم برم مسافرت پیش یکی از دوستام. صبح که می خواستم با ماشین حرکت کنم بهش زنگ زدم گفتم من دارم حرکت می کنم اون از من پرسید حدوداً چه ساعتی از بعد از ظهر می رسی ؟ چون من بعد از ظهر کاری دارم باید برم جایی کارمو انجام بدم ؟ اگر من زمان رسیدنمو با زمان کار دوستم یکسان می گفتم و اون موقع نمی رسیدم هم دوستم به کارش نمی رسید و هم من بد قول می شدم . اگر من زمان رسیدنمو دیرتر می گفتم ولی زودتر می رسیدم باعث اتلاف وقت خودم می شد . پس من از اون خواستم وضعیت ترافیک جاده رو نگاه کنه و به من بگه تا من بر اساس اون وضعیت بتونم بهش بگم . در دنیا Sql server هم همین حالت وجود دارد. برای اجرای پرس و جویی تعداد رکورد های بازگشتی تخمین زده می شود (از روی آمارها) . و نقشه اجرایی بهینه در کمترین زمان ممکن ساخته می شود . و از روی این نقشه پرس و جو اجرا می شود . اگر sql server تخمین اشتباهی بزند پلن انتخابی غیر بهینه می باشد و پرس وجو کند تر اجرا می شود . نکته : ۱- اگر جدولی دارید که تعداد رکوردهای آن بسیار زیاد است . آمارهای آن را به صورت دستی به روز کنید . پاسخ به دیدگاه تورج عزیزی ۳۱ / ۰۵ / ۹۴ - ۰۹:۳۴ جناب طاهری چرا مقاله من ناقص نشون داده شده؟ پاسخ به دیدگاه فرید طاهری ۳۱ / ۰۵ / ۹۴ - ۰۲:۰۳ سلام اوکی شد. در سیستم مقالات یکسری باگ کوچک وجود دارد که امیداورم این هفته رفع شوند. از شما بابت این مشکل عذرخواهی می کنم با تشکر پاسخ به دیدگاه مسعود طاهری ۲۹ / ۰۵ / ۹۴ - ۰۹:۵۸ خیلی عالی بود. هدف Query Selectivity انتخاب یک ایندکس مناسب است و فرمول بدست آوردن آن تعداد کل رکوردهای یک فیلد / تعداد کل رکوردهای غیر تکراری یک فیلد (از چپ بخوانید) Selectivity بالا = افزایش کارایی + استخراج تعداد رکوردهای کمتر یک Selectivity ایده ال = Unique Index و یا Primary Key می باشد در پایان یک کتاب خیلی عالی برای Statistics در این لینک وجود دارد حتما اون را مطالعه کنید پاسخ به دیدگاه مسعود طاهری ۲۹ / ۰۵ / ۹۴ - ۰۹:۵۸ خیلی عالی بود. هدف Query Selectivity انتخاب یک ایندکس مناسب است و فرمول بدست آوردن آن تعداد کل رکوردهای یک فیلد / تعداد کل رکوردهای غیر تکراری یک فیلد (از چپ بخوانید) Selectivity بالا = افزایش کارایی + استخراج تعداد رکوردهای کمتر یک Selectivity ایده ال = Unique Index و یا Primary Key می باشد در پایان یک کتاب خیلی عالی برای Statistics در این لینک وجود دارد حتما اون را مطالعه کنید پاسخ به دیدگاه Hamid J. Fard ۲۹ / ۰۵ / ۹۴ - ۰۷:۵۱ مقاله جالب بود فقط یک مقدار پیچیده کرده اید برای کسانی که از این موضوع بی اطلاع هستند. به صورت ساده اگر Selectivity بالا باشد تعداد داده های یونیک بسیار و Optimizer ایندکس را به صورت Seek میخواند ولی اگر Selectiviy پایین باشد یعنی یک مقدار به تعداد زیادی کپی شده و Optimizer مجبور به Scan ایندکس می شود. به یاد داشته باشید که QO یک زمان محدودی را برای انتخاب پلن دارد Good Enough Plan به اولین پلن نسبتا خوب گفته می شود ولی به پلن ساده و کم هزینه Trival Plan گفته می شود. بسیار عالی! پاسخ به دیدگاه