مشکل Overestimation در SQL Server

مشکل Overestimation در SQL Server

یکی از مهم‌ترین عواملی که بر عملکرد SQL Server تأثیر می‌گذارد، نحوه تخمین تعداد رکوردهایی است که توسط Query Optimizer انجام می‌شود.
SQL Server قبل از اجرای هر Query تلاش می‌کند تعداد سطرهایی که در هر مرحله از اجرا تولید خواهند شد را پیش‌بینی کند.
این فرآیند با عنوان Cardinality Estimation شناخته می‌شود.

گاهی این تخمین بسیار بیشتر از مقدار واقعی است که به آن Overestimation گفته می‌شود.
در چنین شرایطی، SQL Server تصور می‌کند تعداد بسیار زیادی رکورد پردازش خواهد شد، در حالی که داده واقعی بسیار کمتر است.
نتیجه این موضوع، مصرف بیش از حد حافظه، انتخاب Execution Plan نامناسب و کاهش کارایی سیستم خواهد بود.

Overestimation در SQL Server چیست؟

Overestimation زمانی رخ می‌دهد که Query Optimizer تعداد ردیف‌های خروجی یک عملیات را بیشتر از مقدار واقعی تخمین بزند.

برای مثال:

 

• تعداد واقعی رکوردها: ۱,۲۰۰
• تعداد تخمین زده شده: ۱۵۰,۰۰۰

 

در این حالت اختلاف بسیار زیادی میان مقدار واقعی و مقدار تخمین زده شده وجود دارد.

SQL Server بر اساس همین تخمین‌ها تصمیم می‌گیرد:

• چه نوع Join استفاده شود
• چه مقدار حافظه اختصاص یابد
• از چه Indexهایی استفاده شود
• ترتیب اجرای عملیات چگونه باشد

 

اگر تخمین اشتباه باشد، کل Execution Plan تحت تأثیر قرار می‌گیرد.

 

دلایل اهمیت Overestimation  در SQL Server

بسیاری تصور می‌کنند تنها کم‌برآورد (Underestimation) خطرناک است، اما Overestimation نیز می‌تواند مشکلات جدی ایجاد کند.

از جمله:

 

• افزایش مصرف Memory

• افزایش Memory Grant

• انتخاب Hash Join به جای Nested Loop

• استفاده نکردن از Index مناسب

• افزایش زمان اجرای Query

• کاهش همزمانی (Concurrency)

• کاهش Throughput سرور

 

در سیستم‌های پرترافیک، این مشکل می‌تواند عملکرد کل SQL Server را تحت تأثیر قرار دهد.

نحوه عملکرد Query Optimizer در SQL Server

هنگام اجرای Query، SQL Server ابتدا آن را اجرا نمی‌کند.

ابتدا مراحل زیر انجام می‌شود:

 

• بررسی Statistics

• بررسی Indexها

• تخمین تعداد رکوردها

• ساخت Execution Plan

• اجرای Query

اگر مرحله سوم اشتباه باشد، مراحل بعد نیز اشتباه خواهند بود.

 

مهم‌ترین دلایل Overestimation در SQL Server

۱. قدیمی بودن Statistics

رایج‌ترین دلیل Overestimation، قدیمی بودن Statistics است.

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

مثال:

 

جدول ابتدا دارای یک میلیون رکورد بوده است.

اکنون تنها ۵۰ هزار رکورد دارد.

 

اگر Statistics قدیمی باشد، SQL Server همچنان تصور می‌کند جدول شامل یک میلیون رکورد است.

 

۲. توزیع نامناسب داده‌ها (Data Skew)

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

 

مثال:

 

۹۹ درصد رکوردها:

 

Status = Active

 

تنها یک درصد:

 

Status = Deleted

 

اگر Optimizer توزیع واقعی را تشخیص ندهد، ممکن است مقدار بسیار بیشتری را تخمین بزند.

 

۳. Predicateهای پیچیده

استفاده از توابع روی ستون‌ها باعث می‌شود SQL Server نتواند تخمین دقیقی انجام دهد.

مثال:

 

WHERE YEAR(OrderDate)=2025

 

بهتر است:

 

WHERE OrderDate >= '2025-01-01'

AND OrderDate < '2026-01-01'

۴. Parameter Sniffing

گاهی اولین مقدار پارامتر باعث ایجاد Execution Plan می‌شود.

سپس همان Plan برای مقادیر دیگر استفاده می‌شود.

 

در نتیجه تخمین تعداد رکوردها اشتباه خواهد بود.

 

۵. استفاده از Viewهای پیچیده

Viewهای دارای Joinهای متعدد و Subqueryها گاهی باعث ایجاد تخمین نادرست می‌شوند.

 

۶. استفاده از OR

مانند:

 

WHERE City='Berlin'

OR Country='Germany'

 

در بسیاری از موارد، تخمین Optimizer بیشتر از مقدار واقعی خواهد بود.

 

۷. Joinهای متعدد

هرچه تعداد Joinها بیشتر شود، احتمال خطای Cardinality Estimation نیز افزایش پیدا می‌کند.

نشانه‌های Overestimation در SQL Server

برخی علائم عبارت‌اند از:

• Memory Grant بسیار زیاد

• Hash Match غیرضروری

• Sortهای سنگین

• زمان اجرای زیاد

• Parallelism غیرضروری

• Spillهای حافظه

نحوه تشخیص Overestimation در SQL Server

بهترین ابزار:

 

Actual Execution Plan

در Execution Plan دو مقدار مشاهده می‌شود:

 

• Estimated Number of Rows
• Actual Number of Rows

 

اگر اختلاف بسیار زیاد باشد، احتمالاً Overestimation رخ داده است.

 

مثال:

 

Estimated Rows

 

500000

 

Actual Rows

 

3200

 

این اختلاف نشان‌دهنده تخمین نادرست است.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

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

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

استفاده از Live Query Statistics

ابزار Live Query Statistics امکان مشاهده اجرای Query به صورت زنده را فراهم می‌کند.

اگر اختلاف میان Actual و Estimated زیاد باشد، به‌راحتی قابل مشاهده خواهد بود.

 

• بررسی Statistics

 

دستور:

 

DBCC SHOW_STATISTICS('Orders', IX_OrderDate);

 

این دستور اطلاعات دقیقی درباره Histogram و Density نمایش می‌دهد.

 

• بروزرسانی Statistics

یکی از اولین راهکارها:

 

UPDATE STATISTICS Orders;

 

یا:

 

sp_updatestats

 

این کار باعث بهبود دقت تخمین‌ها می‌شود.

 

استفاده از Full Scan

برای دقت بیشتر:

 

UPDATE STATISTICS Orders

WITH FULLSCAN;

 

در این روش کل جدول بررسی می‌شود.

 

ایجاد Index مناسب

اگر Index مناسب وجود نداشته باشد، تخمین‌ها نیز ممکن است اشتباه باشند.

نمونه:

 

CREATE INDEX IX_OrderDate

ON Orders(OrderDate);

حذف Function از شرط‌ها

 

بد:

 

WHERE MONTH(OrderDate)=6

 

خوب:

 

WHERE OrderDate >= '2025-06-01'

AND OrderDate < '2025-07-01'

استفاده از Query Store

Query Store امکان مقایسه Execution Planهای مختلف را فراهم می‌کند.

 

با استفاده از آن می‌توان تغییرات ناشی از Overestimation را شناسایی کرد.

 

استفاده از Extended Events

Extended Events اطلاعات مفیدی درباره Memory Grant و Cardinality Estimation ارائه می‌دهد.

Cardinality Estimator جدید

از SQL Server 2014 به بعد، نسخه جدید Cardinality Estimator معرفی شد.

در برخی سیستم‌ها عملکرد بهتر و در برخی دیگر بدتر است.

می‌توان نسخه قدیمی یا جدید را آزمایش کرد.

 

Memory Grant و Overestimation در SQL Server

یکی از بزرگ‌ترین مشکلات Overestimation، اختصاص بیش از حد حافظه است.

فرض کنید SQL Server تصور می‌کند:

 

10,000,000 Rows

 

در حالی که مقدار واقعی:

 

5,000 Rows

 

در این شرایط حافظه بسیار زیادی رزرو می‌شود.

 

تأثیر بر Joinها

Overestimation معمولاً باعث انتخاب:

 

• Hash Join

 

به جای:

 

• Nested Loop

 

می‌شود.

Hash Join برای داده‌های کم معمولاً کارایی پایین‌تری دارد.

 

تأثیر بر Parallelism

اگر Optimizer تصور کند حجم داده زیاد است، Query را Parallel اجرا می‌کند.

در حالی که شاید اجرای Serial سریع‌تر باشد.

 

بهترین روش‌های جلوگیری از Overestimation

• به‌روزرسانی منظم Statistics

• استفاده از Index مناسب

• حذف Function از شرط‌ها

• بازنویسی Queryهای پیچیده

• بررسی Execution Plan

• استفاده از Query Store

• مانیتورینگ Memory Grant

• طراحی صحیح Indexها

• اجتناب از Predicateهای غیرقابل جستجو (Non-SARGable)

تفاوت Overestimation و Underestimation در SQL Server

ویژگی	Overestimation	Underestimation
تخمین رکورد	بیشتر از واقعیت	کمتر از واقعیت
مصرف حافظه	زیاد	کم
نوع	Join Hash	Join Nested Loop
Memory Grant	زیاد	کم
احتمال Spill	کمتر	بیشتر
سرعت اجرا	معمولاً پایین	معمولاً پایین

جمع‌بندی

Overestimation یکی از مشکلات مهم در SQL Server است که زمانی رخ می‌دهد که Query Optimizer تعداد رکوردهای خروجی را بیش از مقدار واقعی تخمین بزند.
این خطا می‌تواند باعث افزایش مصرف حافظه، انتخاب Execution Plan نامناسب، استفاده غیرضروری از Hash Join و Parallelism و در نهایت کاهش عملکرد پایگاه داده شود.

برای جلوگیری از این مشکل، لازم است Statistics به‌صورت منظم به‌روزرسانی شوند، Queryها به شکل SARGable نوشته شوند، Indexهای مناسب ایجاد شوند و Execution Planها به‌طور مستمر بررسی گردند.
استفاده از ابزارهایی مانند Query Store، Live Query Statistics و Extended Events نیز به شناسایی و رفع سریع‌تر این مشکل کمک می‌کند.

با رعایت این اصول، می‌توان دقت تخمین‌های Query Optimizer را افزایش داد و کارایی SQL Server را در محیط‌های عملیاتی به شکل محسوسی بهبود بخشید.