آپدیت Statistics دوبار غیر ضروری بهینه سازی عملکرد

بهینه‌سازی عملکرد SQL Server: آیا آپدیت Statistics دوبار لازم است یا هرگز؟

بسیاری از متخصصان پایگاه داده SQL Server با توصیه‌ی «Statistics را دوبار آپدیت کنید» آشنا هستند. این توصیه‌ی رایج، به‌ویژه در محیط‌های دیتابیس قدیمی‌تر، بر این فرض استوار است که SQL Server ممکن است در اولین آپدیت Statistics، نتایج دقیقی نداشته باشد. اما آیا این توصیه هنوز معتبر است؟ و در سناریوهای امروزی، بهترین رویکرد برای مدیریت Statistics چیست؟ در این مقاله به بررسی این موضوع می‌پردازیم و نشان می‌دهیم که در بسیاری از موارد، این روش نه تنها ضروری نیست، بلکه می‌تواند بی‌اثر باشد.

مشکل و ریشه‌های توصیه‌ی “آپدیت دوبار”

در هسته‌ی این توصیه، چگونگی جمع‌آوری Statistics توسط SQL Server نهفته است. به‌طور پیش‌فرض، SQL Server برای جمع‌آوری Statistics از نمونه‌گیری (sampling) استفاده می‌کند و نه از اسکن کامل (fullscan) تمام داده‌ها. این نمونه‌گیری به معنای ارزیابی زیرمجموعه‌ای از داده‌ها برای تخمین توزیع مقادیر ستون است. همچنین، سیستم بروزرسانی خودکار Statistics (Auto-آپدیت Statistics) تنها زمانی فعال می‌شود که تغییرات کافی در داده‌ها رخ داده باشد. این دو عامل می‌توانند باعث شوند که Statistics ایجاد شده در برخی شرایط، دقیق نباشند و در نتیجه، Query Optimizer ما Execution Plans  ناکارآمدی تولید کند.

به همین دلیل، برخی متخصصان برای اطمینان از دقت Statistics، پیشنهاد می‌کنند که Statistics را دو بار آپدیت کنید. اعتقاد بر این است که اولین آپدیت ممکن است نمونه‌گیری اولیه را بهبود بخشد و دومین آپدیت بر اساس داده‌های نمونه‌برداری شده‌ی بهتر، Statistics دقیق‌تری را تولید کند. اما با نگاهی عمیق‌تر به مکانیزم‌های SQL Server، می‌توانیم این فرضیه را بررسی کنیم.

آماده‌سازی برای تست

برای نشان دادن این موضوع، یک سناریو ساده ایجاد می‌کنیم. ما یک جدول با مقادیر تصادفی ایجاد می‌کنیم و سپس Statistics را آپدیت کرده و تأثیر آن را بر روی Execution Plan یک کوئری بررسی می‌کنیم.

ابتدا، یک دیتابیس جدید و یک جدول به نام `SampleData` ایجاد می‌کنیم:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDB')
BEGIN
    ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TestDB;
END
GO

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE SampleData
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Value INT
);

سپس، ۱۰۰۰۰۰ سطر داده تصادفی را به جدول وارد می‌کنیم. مقادیر `Value` بین ۱ تا ۱۰۰۰ خواهند بود تا توزیع داده‌ها قابل مشاهده باشد:

DECLARE @i INT = 0;
WHILE @i < 100000
BEGIN
    INSERT INTO SampleData (Value)
    SELECT CAST(RAND() * 1000 AS INT);

    SET @i = @i + 1;
END
GO

توجه داشته باشید که تابع `RAND()` یک عدد اعشاری بین ۰ و ۱ تولید می‌کند. برای تولید اعداد صحیح بین ۱ تا ۱۰۰۰، از فرمول زیر استفاده می‌کنیم:

RAND() * 1000

و سپس آن را به نوع `INT` تبدیل می‌کنیم:

CAST(RAND() * 1000 AS INT)

با این کار، توزیع داده‌ها نسبتاً یکنواخت خواهد بود.

وضعیت اولیه Statistics

در این مرحله، SQL Server هنوز هیچ Statisticsی برای ستون `Value` ایجاد نکرده است. می‌توانیم وجود Statistics را با دستور زیر بررسی کنیم:

DBCC SHOW_STATISTICS('SampleData', _WA_Sys_00000002_0CBA6B4A)

این دستور خطایی را برمی‌گرداند زیرا هیچ Statisticsی به نام `_WA_Sys_00000002_0CBA6B4A` وجود ندارد (این نام یک Statistics خودکار است که توسط SQL Server تولید می‌شود).

حالا یک کوئری ساده اجرا می‌کنیم تا ببینیم Query Optimizer چه طرحی را انتخاب می‌کند. این کوئری به طور پیش‌فرض، Statistics را به صورت خودکار ایجاد می‌کند:

SELECT *
FROM SampleData
WHERE Value = 500;
GO

پس از اجرای کوئری، اگر دوباره Statistics را بررسی کنیم، خواهیم دید که یک Statistics برای ستون `Value` ایجاد شده است:

DBCC SHOW_STATISTICS('SampleData', _WA_Sys_00000002_0CBA6B4A) WITH HISTOGRAM;

با بررسی خروجی، می‌توانیم `Rows Sampled` را ببینیم که نشان می‌دهد SQL Server چند سطر را برای ایجاد Statistics نمونه‌برداری کرده است. در این مثال، احتمالاً عدد نسبتاً کمی (مثلاً ۲۰۰۰۰ تا ۳۰۰۰۰ سطر از ۱۰۰۰۰۰ سطر) خواهد بود.

اولین آپدیت Statistics (بدون FULLSCAN)

حالا Statistics را یک بار آپدیت می‌کنیم، اما بدون استفاده از گزینه‌ی `FULLSCAN`. این شبیه‌سازی همان کاری است که معمولاً بدون تعیین گزینه‌ای خاص انجام می‌شود.

UPDATE STATISTICS SampleData(_WA_Sys_00000002_0CBA6B4A);
GO

پس از آپدیت، دوباره Statistics و Execution Plan را بررسی می‌کنیم:

DBCC SHOW_STATISTICS('SampleData', _WA_Sys_00000002_0CBA6B4A) WITH HISTOGRAM;
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT *
FROM SampleData
WHERE Value = 500;
GO

گزینه‌های `SET STATISTICS IO ON;` و `SET STATISTICS TIME ON;` برای مشاهده منابع مصرفی کوئری مفید هستند:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

با بررسی `Rows Sampled`، متوجه می‌شویم که این مقدار احتمالاً تغییر چندانی نکرده است. در واقع، SQL Server در هر بار آپدیت Statistics (بدون `FULLSCAN`)، همان مکانیزم نمونه‌گیری پیش‌فرض را به کار می‌برد.

دومین آپدیت Statistics

حالا طبق توصیه‌ی “آپدیت دوبار”، یک بار دیگر Statistics را آپدیت می‌کنیم:

UPDATE STATISTICS SampleData(_WA_Sys_00000002_0CBA6B4A);
GO

و مجدداً Statistics و عملکرد کوئری را بررسی می‌کنیم:

DBCC SHOW_STATISTICS('SampleData', _WA_Sys_00000002_0CBA6B4A) WITH HISTOGRAM;
GO

SELECT *
FROM SampleData
WHERE Value = 500;
GO

در کمال تعجب، مشاهده می‌کنیم که `Rows Sampled` احتمالاً همان مقدار باقی مانده است. این نشان می‌دهد که تکرار دستور `آپدیت STATISTICS` بدون تغییر گزینه‌های آن، معمولاً منجر به جمع‌آوری داده‌های بیشتر برای Statistics نمی‌شود. SQL Server در هر بار اجرا، بر اساس الگوریتم نمونه‌برداری داخلی خود تصمیم می‌گیرد که چند سطر را نمونه‌برداری کند، و این الگوریتم با هر بار اجرا بدون تغییرات اساسی در داده‌ها یا پارامترهای دستور، تغییر چشمگیری نمی‌کند.

چرا آپدیت دوبار منطقی نیست؟

دلیل اصلی بی‌تأثیری آپدیت مکرر Statistics (بدون `FULLSCAN`) در درازمدت، این است که SQL Server از یک معیار نمونه‌گیری داخلی استفاده می‌کند. این معیار به اندازه جدول و میزان تغییرات بستگی دارد. وقتی شما `آپدیت STATISTICS` را اجرا می‌کنید، اگر گزینه‌ی `FULLSCAN` را مشخص نکنید، SQL Server از گزینه‌ی پیش‌فرض `SAMPLE` استفاده می‌کند. این گزینه به‌طور داخلی تعیین می‌کند که چند درصد از داده‌ها را نمونه‌برداری کند:

WITH SAMPLE 25 PERCENT

این درصد نمونه‌برداری می‌تواند متغیر باشد و توسط SQL Server برای به دست آوردن دقت کافی در Statistics تعیین می‌شود. دو بار اجرای دستور `آپدیت STATISTICS` با این گزینه‌ی پیش‌فرض (یا بدون تعیین صریح `SAMPLE`) معمولاً منجر به تغییر در درصد نمونه‌برداری یا دقت هیستوگرام نمی‌شود. اگر Statistics قبلاً در حد قابل قبولی دقیق باشد، اجرای مجدد آن تأثیری نخواهد داشت. اگر Statistics به دلیل تغییرات داده‌ها قدیمی شده باشد، یک بار آپدیت کافی است تا SQL Server بر اساس نمونه‌گیری جدید، آن را بروزرسانی کند.

رویکردهای جایگزین برای دقت Statistics

به جای آپدیت دوبار، راه‌های موثرتری برای اطمینان از دقت Statistics وجود دارد:

1. استفاده از `FULLSCAN`:
مطمئن‌ترین راه برای داشتن Statistics دقیق، استفاده از گزینه‌ی `FULLSCAN` است. این گزینه به SQL Server می‌گوید که تمام داده‌های جدول را اسکن کند تا هیستوگرام و چگالی‌های دقیق‌تری ایجاد کند. این روش برای جداول بزرگ می‌تواند زمان‌بر باشد، اما بالاترین دقت را تضمین می‌کند.

    UPDATE STATISTICS SampleData(_WA_Sys_00000002_0CBA6B4A) WITH FULLSCAN;
    GO
    

با اجرای مجدد `DBCC SHOW_STATISTICS`، خواهید دید که `Rows Sampled` اکنون برابر با `Rows` کل جدول (۱۰۰۰۰۰) است که نشان‌دهنده دقت ۱۰۰٪ است.

2. تنظیم نرخ نمونه‌برداری (SAMPLE PERCENT):
اگر نمی‌توانید از `FULLSCAN` استفاده کنید، می‌توانید درصد نمونه‌برداری را به صورت دستی تعیین کنید. این به شما کنترل بیشتری بر میزان داده‌های نمونه‌برداری شده می‌دهد.

    UPDATE STATISTICS SampleData(_WA_Sys_00000002_0CBA6B4A) WITH SAMPLE 50 PERCENT;
    GO
    

این گزینه می‌تواند تعادلی بین سرعت و دقت ایجاد کند، اما باید با احتیاط استفاده شود تا از نمونه‌برداری ناکافی جلوگیری شود.

3. اعتماد به بروزرسانی خودکار Statistics (Auto-آپدیت Statistics):
برای بسیاری از جداول، مکانیزم بروزرسانی خودکار Statistics SQL Server به اندازه کافی هوشمند است که Statistics را به موقع و با دقت مناسب بروزرسانی کند. این مکانیزم زمانی فعال می‌شود که تغییرات قابل توجهی در داده‌ها (مثلاً ۲۰٪ تغییر برای جداول بزرگ یا تعداد معینی تغییر برای جداول کوچک) رخ داده باشد. اگر `Auto-آپدیت Statistics` فعال باشد، SQL Server به طور خودکار Statistics را در پس‌زمینه بروزرسانی می‌کند.

برای بررسی وضعیت این گزینه:

    SELECT name, is_auto_create_stats_on, is_auto_update_stats_on
    FROM sys.databases
    WHERE name = 'TestDB';
    

اگر این گزینه‌ها `ON` باشند، در بیشتر موارد نیازی به مداخله دستی نخواهید داشت.

نتیجه‌گیری

توصیه‌ی قدیمی “آپدیت Statistics دوبار” در SQL Server، در بسیاری از سناریوهای مدرن نه تنها بی‌اثر است، بلکه می‌تواند اتلاف منابع و زمان باشد. مکانیزم‌های نمونه‌برداری پیش‌فرض SQL Server معمولاً در هر بار اجرا (بدون `FULLSCAN`) الگوی مشابهی را دنبال می‌کنند. برای اطمینان از دقت Statistics و در نتیجه بهبود عملکرد کوئری و Execution Plans، رویکردهای موثرتری مانند استفاده از `WITH FULLSCAN`، تعیین نرخ `SAMPLE` به صورت دستی، یا اعتماد به مکانیزم `Auto-آپدیت Statistics` وجود دارد.

برای بهینه‌سازی واقعی پایگاه داده SQL Server و دستیابی به عملکرد بهینه، تمرکز بر درک چگونگی کارکرد Statistics، پایش `Execution Plans` و استفاده هدفمند از ابزارهای موجود، بسیار کارآمدتر از پیروی از توصیه‌های منسوخ شده است. همیشه بهترین رویکرد، آزمایش و پایش در محیط خاص شماست. با انتخاب استراتژی مناسب برای مدیریت Statistics، می‌توانید از ثبات و کارایی بالای سیستم خود اطمینان حاصل کنید.