به روز رسانی و بازسازی Statistics

در مطلب Statistics چیست؟ با مفهوم و کاربرد Statistics آشنا شدید. حالا با فرض اینکه Statistics ایجاد شده است، اگر عملیات Insert/Update/Delete اتفاق افتاد، Statsها چگونه به روز رسانی می شوند و آیا امکان انجام به روز رسانی Statistics بصورت دستی وجود دارد؟

یکی دیگر از عواملی که می تواند در افزایش سرعت و کارآیی پایگاه داده موثر باشد، به روز بودن داده های Statistics هاست. همان طور که می دانید وظیفه Statistics ها، انتخاب یک ایندکس مناسب برای کوئری هاست. بنابراین اگر statistics ها به روز نباشند، ایندکس مناسب انتخاب نخواهد شد.

از طریق تنظیمات پایگاه داده و یا جدول می توان عملیات اتوماتیک به روز رسانی Statistics ها را فعال یا غیرفعال نمود.

روش های مختلف به روز رسانی Statistics ها:

1. در حین انجام عملیات Query Optimization به روز رسانی انجام می شود.
2. در حین Rebuild ایندکس ها به روز رسانی Stats ها انجام می شود.
3. با استفاده از دستور Update Statistics می توان به صورت دستی stats را بازسازی نمود.

نحوه ی تشخیص به روز بودن یا نبودن Statistisc

SQL Server با استفاده از فیلد ColModCtr می تواند به روز بودن یا نبودن Statistics را آنالیز کند.

در SQL Server مفهموی به نام ColModCtr یا Column Modification Counter وجود دارد و هر بار که مقدار یک فیلد را تغییر می دهید (update/Insert/Delete) به ازای آن یک واحد به ColModCtr اضافه می شود. توجه داشته باشید که مقدار ColModCtr به ازای هر به روز رسانی Statistics صفر خواهد شد.

متأسفانه فیلد ColModCtr یک فیلد مخفی است و قابل دسترس نیست.

بنابراین با توجه به فیلد ColModCtr می توان حدث زد که برای به روز رسانی خودکار Statistics شرایطی باید محیا شود...!

شرایط به روز رسانی Statistics

با توجه به فیلد ColModCtr و تعداد رکوردهای جدول، اگر یکی از 3 حالت زیر رخ دهد، Statisticsها به روز رسانی می شوند:

1. جدول خالی باشد: با درج اولین رکورد، کل Statisticsهای جدول مذکور به روز رسانی می شوند.
2. تعداد رکوردهای جدول کوچکتر از 500 باشد: در این صورت مقدار فیلد ColModCtr باید بزرگتر یا مساوی 500 باشد تا  Statisticsهای جدول مذکور به روز رسانی شوند.
3. تعداد رکوردهای جدول بزرگتر از 500 باشد: در این صورت مقدار فیلد ColModCtr باید بزرگتر یا مساوی 500 بعلاوه ی 20 درصد کل رکوردهای جدول باشد، تا  Statisticsهای جدول مذکور به روز رسانی شوند.

نکته مهم: تصور فرمایید که شرایط برای به روز رسانی Statistics محیا شده است، حالا اگر کوئری ای اجرا شود که قسمت Where آن، Query Optimizer را به سمت استفاده از Statistics مذکور راهنمایی کند، در آن موقع Statistics به روز رسانی خواهد شد. البته به روز رسانی خودکار باید فعال باشد.

فعال یا غیرفعال کردن به روز رسانی خودکار Statistics ها به ازای پایگاه داده

بصورت پیشفرض عملیات به روز رسانی Statisticها فعال است، اما برای غیر فعال کردن آن،  در پنل Object Explorer روی پایگاه داده مورد نظرتان راست کلیک کرده و گزینه Properties را انتخاب نمایید، سپس در پنجره Database properties از سمت چپ گزینه Option را انتخاب نمایید و نهایتاً از سمت راست پنجره گزینه Auto Update Statistics را تنظیم نمایید:

مثال 1: اسکریبت تنظیم گزینه AUTO_CREATE_STATISTICS

همان طور که قبلاً اشاره شد با استفاده از گزینه ی AUTO_CREATE_STATISTICS، عملیات ساخت Stats به صورت اتوماتیک انجام خواهد شد:

ALTER DATABASE Northwind 
    SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT

مثال 2: اسکریبت تنظیم گزینه AUTO_UPDATE_STATISTICS

با فعال کردن این گزینه عملیات به روز رسانی به صورت اتوماتیک انجام می شود:

ALTER DATABASE Northwind 
    SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT

مثال 3: اسکریبت تنظیم گزینه AUTO_UPDATE_STATISTICS_ASYNC

زمانی که یک کوئری برای اجرا به پایگاه داده ارسال می شود، اگر Statistics به روز نباشد، کوئری مذکور Lock می شود تا Statisticsها به روز شوند و یک فقفه کوتاه اتفاق خواهد افتاد، اما با فعال کردن گزینه AUTO_UPDATE_STATISTICS_ASYNC کوئری اجرا شده و همزمان عملیات به روز رسانی انجام خواهد شد:

ALTER DATABASE Northwind 
    SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

توجه: جداولی که جستجو بر روی داده های آنها به ندرت انجام می شود و بیشتر عملیات درج (Insert) در آنها اتفاق می افتد، کاندید مناسبی برای غیرفعال کردن به روز رسانی Statsها هستند. (مانند جداولی که Log سیستم را نگهداری می کنند)

فعال یا غیرفعال کردن  به روز رسانی خودکار Statistics به ازای یک جدول

به ازای یک جدول نیز می توان گزینه به روز رسانی اتوماتیک را فعال یا غیرفعال کرد:

EXEC sp_autostats 'Customers'
EXEC sp_autostats 'Customers','OFF'

در مثال بالا، با استفاده از پروسیجر sp_autostats و تنظیم پارامتر اول با نام جدول لیست تمام Stats های جدول Customers به همراه وضعیت فعال یا غیرفعال بودن به روز رسانی اتوماتیک هر کدام، نمایش داده می شود، حالا اگر پارامتر دوم پروسیجر sp_autostats را با مقدار OFF تنظیم کنیم، به روز رسانی خودکار، غیرفعال خواهد شد.

توجه: با وجود حالت AUTO_UPDATE_STATISTICS_ASYNC دیگر، نیازی به غیر فعال کردن به روز رسانی اتوماتیک نیست.

به روز رسانی تمام Statisticsهای پایگاه داده بصورت دستی

پروسیجر sp_updatestats تمام Statisticsهای جداول را به صورت دستی به روز رسانی خواهد کرد:

EXEC sp_updatestats

در ادامه بحث Statistics خواهید دید...!

با خرید جلسه 6 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. قابلیت Compatibility Level چیست و چگونه باعث افزایش کارایی در SQL Server 2014 می شود.

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 6
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Update Statistics) در ویدئو:  12:48

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.