راهنمای جامع تنظیمات Performance در سطح پایگاه داده SQL Server

Database Scoped Configuration یکی از مهم‌ترین ابزارهای SQL Server برای جداکردن سیاست‌های عملکردی یک Database از تنظیمات سراسری Instance است. وقتی یک سرور هم‌زمان OLTP، گزارش‌گیری، Data Warehouse یا نرم‌افزار قدیمی مهاجرت‌شده را میزبانی می‌کند، یک سیاست واحد برای همه بارها همیشه منطقی نیست.

این مجموعه روی ۱۷ تنظیمی تمرکز دارد که با Query Optimizer، Cardinality Estimation، Parallelism، Memory Grant، Intelligent Query Processing، Query Store و Plan Cache درگیرند. هر گزینه مقاله مستقل دارد تا تصمیم Production به چند خط توضیح و یک Toggle ساده محدود نشود.

اصل مشترک همه این موضوع‌ها این است: Feature خوب لزوماً برای هر Workload خوب نیست. نسخه Engine، Compatibility Level، شکل داده، Query Plan و هم‌زمانی تعیین می‌کنند که نتیجه واقعی چه باشد. بنابراین Baseline، تغییر کوچک، اندازه‌گیری و Rollback چهار ستون تصمیم هستند.

دسترسی سریع به مقاله‌های تخصصی

• آموزش کامل MAXDOP — تنظیم MAXDOP در سطح پایگاه داده
• آموزش کامل LEGACY_CARDINALITY_ESTIMATION — مدیریت Legacy Cardinality Estimation
• آموزش کامل PARAMETER_SNIFFING — کنترل Parameter Sniffing در SQL Server
• آموزش کامل QUERY_OPTIMIZER_HOTFIXES — فعال‌سازی Query Optimizer Hotfixes
• آموزش کامل BATCH_MODE_MEMORY_GRANT_FEEDBACK — کنترل Batch Mode Memory Grant Feedback
• آموزش کامل ROW_MODE_MEMORY_GRANT_FEEDBACK — کنترل Row Mode Memory Grant Feedback
• آموزش کامل MEMORY_GRANT_FEEDBACK_PERSISTENCE — ماندگاری Memory Grant Feedback
• آموزش کامل MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT — Percentile Memory Grant Feedback
• آموزش کامل BATCH_MODE_ADAPTIVE_JOINS — کنترل Batch Mode Adaptive Joins
• آموزش کامل BATCH_MODE_ON_ROWSTORE — Batch Mode on Rowstore
• آموزش کامل TSQL_SCALAR_UDF_INLINING — Scalar UDF Inlining در T-SQL
• آموزش کامل INTERLEAVED_EXECUTION_TVF — Interleaved Execution برای Multi-Statement TVF
• آموزش کامل DEFERRED_COMPILATION_TV — Deferred Compilation برای Table Variable
• آموزش کامل DOP_FEEDBACK — DOP Feedback در SQL Server
• آموزش کامل CE_FEEDBACK — Cardinality Estimation Feedback
• آموزش کامل OPTIMIZED_PLAN_FORCING — Optimized Plan Forcing
• آموزش کامل CLEAR_PROCEDURE_CACHE — پاک‌سازی Procedure Cache در سطح پایگاه داده

نقشه ذهنی تنظیمات Performance

برای فهم بهتر، این گزینه‌ها را در چند خانواده ببینید: Parallelism، Cardinality و Optimizer، Memory Grant Feedback، Adaptive Execution، Plan Feedback و Plan Cache. این دسته‌بندی کمک می‌کند به‌جای روشن‌کردن تصادفی Featureها، اول لایه مشکل را پیدا کنید.

در این معماری، Optimizer و Query Store نقش پل را دارند. بعضی قابلیت‌ها هنگام Compile تصمیم می‌گیرند، بعضی در Runtime بازخورد می‌گیرند و بعضی تجربه اجرا را برای دفعات بعد نگه می‌دارند. به همین دلیل مشاهده Plan واقعی و Runtime هم‌زمان ضروری است.

Parallelism و ظرفیت CPU

MAXDOP سیاست پایه موازی‌سازی را محدود می‌کند و DOP Feedback از رفتار Queryهای تکراری برای تنظیم بهتر DOP کمک می‌گیرد. اولی Policy است و دومی Feedback؛ جای هم را نمی‌گیرند.

تنظیم MAXDOP در سطح پایگاه داده

در سروری که چند Database با بارهای متفاوت دارد، MAXDOP دیتابیس‌محور اجازه می‌دهد OLTP و بار تحلیلی سیاست یکسانی نداشته باشند. مقدار صفر به رفتار سطح سرور برمی‌گردد و Query Hint همچنان می‌تواند برای Statement خاص اولویت داشته باشد. مطالعه آموزش کامل MAXDOP با مثال‌های عملی

DOP Feedback در SQL Server

DOP Feedback رفتار Queryهای تکراری را مشاهده می‌کند و در صورت تشخیص موازی‌سازی نامناسب می‌تواند DOP مؤثر را در اجراهای بعدی تنظیم کند. هدف تعادل سرعت تک Query و Throughput کل است. مطالعه آموزش کامل DOP_FEEDBACK با مثال‌های عملی

Cardinality و رفتار Optimizer

این گروه روی تخمین تعداد ردیف، مقدار پارامتر هنگام Compile، اصلاحات Optimizer و بازخورد CE تمرکز دارد. Statistics و Data Skew همیشه باید قبل از تغییر بررسی شوند.

مدیریت Legacy Cardinality Estimation

Cardinality Estimator تعداد ردیف‌های احتمالی را پیش‌بینی می‌کند و این تخمین روی Join، Memory Grant و Index Access اثر دارد. Legacy CE بیشتر ابزار سازگاری و عیب‌یابی است تا گزینه‌ای که بدون تحلیل دائماً روشن بماند. مطالعه آموزش کامل LEGACY_CARDINALITY_ESTIMATION با مثال‌های عملی

کنترل Parameter Sniffing در SQL Server

Parameter Sniffing معمولاً مفید است چون Plan را با مقدار واقعی Compile می‌کند، اما در داده‌های بسیار نامتوازن یک Plan ذخیره‌شده ممکن است نماینده همه پارامترها نباشد. خاموش‌کردن سراسری باید آخرین انتخاب باشد. مطالعه آموزش کامل PARAMETER_SNIFFING با مثال‌های عملی

فعال‌سازی Query Optimizer Hotfixes

این گزینه راهی برای فعال‌کردن مجموعه اصلاحات Optimizer در سطح Database است و برای Rollout مرحله‌ای تغییر رفتار پس از CU مفید است. باید با Regression Test و Query Store همراه شود. مطالعه آموزش کامل QUERY_OPTIMIZER_HOTFIXES با مثال‌های عملی

Cardinality Estimation Feedback

CE Feedback وقتی بعضی فرض‌های مدل تخمین باعث خطای پایدار می‌شوند، می‌تواند رفتار مناسب‌تری برای اجراهای بعدی اعمال کند و از Plan Feedback کمک بگیرد. مطالعه آموزش کامل CE_FEEDBACK با مثال‌های عملی

Memory Grant و پایداری حافظه

Grant زیاد حافظه را اشغال و Grant کم Spill ایجاد می‌کند. Feedback، Persistence و Percentile برای Queryهای تکرارشونده تلاش می‌کنند این تعادل را بهتر کنند.

کنترل Batch Mode Memory Grant Feedback

Grant بیش‌ازحد حافظه را بی‌دلیل رزرو می‌کند و Grant کم Spill به tempdb می‌سازد. Feedback در Batch Mode برای Queryهای تکرارشونده تلاش می‌کند اندازه Grant را متعادل کند. مطالعه آموزش کامل BATCH_MODE_MEMORY_GRANT_FEEDBACK با مثال‌های عملی

کنترل Row Mode Memory Grant Feedback

Row Mode Memory Grant Feedback دامنه اصلاح Grant را به Queryهای Row Mode گسترش می‌دهد و برای Sort و Hashهای تکراری با Grant نامناسب کاربرد دارد. مطالعه آموزش کامل ROW_MODE_MEMORY_GRANT_FEEDBACK با مثال‌های عملی

ماندگاری Memory Grant Feedback

بدون Persistence، Feedback می‌تواند به عمر Plan در Cache وابسته باشد. با ماندگارشدن، تجربه اجرا از طریق Query Store حفظ می‌شود و بعد از Restart سریع‌تر قابل استفاده است. مطالعه آموزش کامل MEMORY_GRANT_FEEDBACK_PERSISTENCE با مثال‌های عملی

Percentile Memory Grant Feedback

در Queryهایی که مصرف حافظه بین اجراها تغییر می‌کند، نگاه به مجموعه اجراهای قبلی می‌تواند Grant باثبات‌تری بسازد و رفتار رفت‌وبرگشتی را کمتر کند. مطالعه آموزش کامل MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT با مثال‌های عملی

Adaptive و Intelligent Query Processing

این قابلیت‌ها محدودیت برخی تصمیم‌های Compile سنتی را با Runtime Decision، Inlining یا Compilation دیرتر کاهش می‌دهند. هر Feature شرایط واجد صلاحیت خودش را دارد.

کنترل Batch Mode Adaptive Joins

Adaptive Join بخشی از تصمیم Join را از Compile به Runtime منتقل می‌کند. Plan یک آستانه دارد و بعد از روشن‌شدن اندازه واقعی ورودی، مسیر مناسب‌تر انتخاب می‌شود. مطالعه آموزش کامل BATCH_MODE_ADAPTIVE_JOINS با مثال‌های عملی

Batch Mode on Rowstore

Batch Mode می‌تواند پردازش دسته‌ای را کارآمدتر کند و برای برخی Queryهای تحلیلی Rowstore مصرف CPU را کاهش دهد. روشن بودن گزینه تضمین نمی‌کند هر Query Batch Mode شود. مطالعه آموزش کامل BATCH_MODE_ON_ROWSTORE با مثال‌های عملی

Scalar UDF Inlining در T-SQL

Scalar UDF سنتی می‌تواند هزینه واقعی خود را از Optimizer پنهان کند. Inlining در شرایط واجد صلاحیت منطق تابع را وارد Plan اصلی می‌کند تا Optimizer دید بهتری داشته باشد. مطالعه آموزش کامل TSQL_SCALAR_UDF_INLINING با مثال‌های عملی

Interleaved Execution برای Multi-Statement TVF

MSTVFها historically می‌توانند با تخمین ثابت Plan نامناسب بسازند. Interleaved Execution Optimization را موقتاً متوقف می‌کند، نتیجه TVF را می‌بیند و ادامه Plan را با تخمین واقع‌بینانه‌تری می‌سازد. مطالعه آموزش کامل INTERLEAVED_EXECUTION_TVF با مثال‌های عملی

Deferred Compilation برای Table Variable

Table Variableها historically با تخمین‌های ساده می‌توانستند Plan ضعیف بسازند. Deferred Compilation زمان Compile را عقب می‌اندازد تا Optimizer تعداد ردیف واقعی‌تری ببیند. مطالعه آموزش کامل DEFERRED_COMPILATION_TV با مثال‌های عملی

Plan Stability و Cache

Optimized Plan Forcing با مسیر بازتولید Forced Plan در Query Store مرتبط است، در حالی که Clear Procedure Cache ابزار عملیاتی برای حذف Planهای Cache‌شده است. یکی را نباید درمان دیگری دانست.

Optimized Plan Forcing

Plan Forcing برای ثبات مهم است، اما بازتولید Plan اجباری می‌تواند هزینه Optimization داشته باشد. Optimized Plan Forcing برای بعضی Planها Replay Script نگه می‌دارد تا مسیر تولید دوباره کوتاه‌تر شود. مطالعه آموزش کامل OPTIMIZED_PLAN_FORCING با مثال‌های عملی

پاک‌سازی Procedure Cache در سطح پایگاه داده

Clear کردن Procedure Cache برای تست کنترل‌شده یا خروج از Plan نامناسب مفید است، اما عملیات روزمره بی‌خطر نیست. بعد از آن Queryها دوباره Compile می‌شوند و CPU یا Latency موقتاً بالا می‌رود. مطالعه آموزش کامل CLEAR_PROCEDURE_CACHE با مثال‌های عملی

جدول مقایسه سریع

روش استاندارد تغییر در Production

یک Change حرفه‌ای با Command شروع نمی‌شود؛ با فرضیه شروع می‌شود. «Query گزارش فروش به دلیل Memory Grant کم Spill می‌کند» قابل سنجش است، اما «SQL کند است، چند Feature را ON کنیم» فرضیه نیست. در حالت دوم حتی اگر سیستم بهتر شود، علت بهبود معلوم نیست.

1. مسئله را با Query ID، زمان رخداد و Metric تعریف کنید.
2. Baseline را از Query Store، Execution Plan و DMV مرتبط ثبت کنید.
3. نسخه Engine و Compatibility Level را بررسی کنید.
4. فقط یک تغییر را در هر آزمایش اعمال کنید.
5. چند اجرای قابل مقایسه بسنجید.
6. اثر تک Query و کل Workload را جدا تحلیل کنید.
7. در Regression فوراً Rollback و سپس Root Cause Analysis کنید.

جریان استاندارد از Baseline به Change، Compile یا Runtime و سپس Measure می‌رود. حذف هر مرحله، تصمیم را از مهندسی به حدس نزدیک می‌کند.

۶ مثال عملی ترکیبی

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
ORDER BY name;

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

SELECT desired_state_desc, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;

SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT session_id, requested_memory_kb, granted_memory_kb, used_memory_kb
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

SELECT TOP (10) execution_count,total_worker_time,total_elapsed_time
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

چرا Query Store مهم است

Query Store تاریخچه Query، Plan و Runtime را نگه می‌دارد و برای مقایسه قبل و بعد، Plan Regression و Plan Forcing ابزار کلیدی است. Memory Grant Feedback Persistence برای ماندگاری بازخورد به Query Store در حالت READ_WRITE وابسته است و Plan Feedbackهای جدید نیز از زیرساخت Query Store قابل مشاهده‌اند.

Query Store را هم نباید فقط روشن کرد و فراموش کرد. Size، Capture Policy، Read Only شدن و Retention باید مدیریت شوند. داده تاریخی ناقص، تحلیل Performance را هم ناقص می‌کند.

نسخه و Compatibility Level

Database Scoped Configuration از SQL Server 2016 وارد شد، اما همه گزینه‌های این مجموعه در همان نسخه وجود ندارند. Batch Mode on Rowstore و Scalar UDF Inlining با SQL Server 2019 و Compatibility Level 150 شناخته می‌شوند؛ Persistence و Percentile Memory Grant Feedback، DOP Feedback، CE Feedback و Optimized Plan Forcing از قابلیت‌های مهم SQL Server 2022 هستند.

نسخه Engine و Compatibility Level دو متغیر مستقل‌اند. ممکن است Engine جدید باشد ولی Database هنوز روی سطح سازگاری قدیمی اجرا شود. در مهاجرت، CU و تنظیمات Query Store را هم کنار این دو ثبت کنید.

Best Practice نهایی ساده است: Baseline، Change، Measure و Rollback. هیچ Feature هوشمندی جای این چرخه مهندسی را نمی‌گیرد.

سؤالات متداول

Database Scoped Configuration چیست؟

مجموعه تنظیماتی برای کنترل رفتارهای مشخص Database Engine در سطح یک پایگاه داده است تا لازم نباشد همه Workloadهای یک Instance سیاست یکسانی داشته باشند.

آیا همه گزینه‌های این مجموعه را باید ON کنیم؟

خیر. بعضی پیش‌فرض مناسب دارند، بعضی فقط در نسخه یا Compatibility مشخص معنا دارند و CLEAR PROCEDURE_CACHE اصلاً Toggle نیست.

برای شروع Performance Tuning کدام مهم‌تر است؟

اول مشکل را پیدا کنید: Query پرهزینه، Wait، Blocking، Statistics یا Index. سپس تنظیمی را بررسی کنید که مستقیم به علت مشاهده‌شده مربوط است.

آیا این Featureها جای Index Tuning را می‌گیرند؟

خیر. Query غیرSARGable، Index نامناسب و Statistics ضعیف همچنان باید ریشه‌ای اصلاح شوند.

Query Store چه نقشی دارد؟

تاریخچه Plan و Runtime، تشخیص Regression، Plan Forcing و مشاهده برخی Feedbackها را فراهم می‌کند.

برای پروژه تجاری چگونه تغییر دهیم؟

با Change Window، Baseline، تست بار، Rollback و معیار موفقیت. تغییر بدون داده قبل و بعد ریسک تشخیص را بالا می‌برد.

رایج‌ترین اشتباه چیست؟

تغییر چند Feature هم‌زمان و بعد نسبت‌دادن نتیجه به یکی از آنها بدون امکان اثبات.

چه Metricهایی مهم‌اند؟

CPU، Duration، Reads، Waits، Compile، Memory Grant، Spill، Worker و Throughput؛ Metric باید با Feature هم‌راستا باشد.

Rollback خوب چه ویژگی دارد؟

Command معکوس از قبل آماده است و بعد از اجرا مقدار تنظیم، Planها و Baseline دوباره کنترل می‌شوند.

سازگاری نسخه‌ای چگونه بررسی می‌شود؟

نسخه Engine، Compatibility Level و مستندات همان Feature را با هم ببینید؛ وجود Syntax به معنی رفتار یکسان در همه سطوح نیست.

سؤالات مصاحبه

1. تفاوت Database Scoped و Server-level Configuration چیست؟
2. چرا Compatibility Level در IQP مهم است؟
3. برای Regression چه داده‌ای جمع می‌کنید؟
4. چه زمانی Query-level Hint بهتر از تغییر Database-wide است؟
5. چرا Clear Procedure Cache راه‌حل دائمی نیست؟
6. Query Store چگونه به Plan Stability کمک می‌کند؟

جمع‌بندی

این ۱۷ گزینه را Toolbox ببینید، نه Checklist برای ON کردن. Parallelism، Cardinality، Memory Grant، Adaptive Execution و Plan Stability هرکدام مسئله متفاوتی دارند. تشخیص درست یعنی انتخاب ابزار متناسب با مسئله و اندازه‌گیری نتیجه.

برای مطالعه عمیق، مقاله‌های زیر هر کدام ۱۰ مثال، خروجی نمونه، FAQ، خطاهای رایج و Best Practice دارند.

• MAXDOP — تنظیم MAXDOP در سطح پایگاه داده
• LEGACY_CARDINALITY_ESTIMATION — مدیریت Legacy Cardinality Estimation
• PARAMETER_SNIFFING — کنترل Parameter Sniffing در SQL Server
• QUERY_OPTIMIZER_HOTFIXES — فعال‌سازی Query Optimizer Hotfixes
• BATCH_MODE_MEMORY_GRANT_FEEDBACK — کنترل Batch Mode Memory Grant Feedback
• ROW_MODE_MEMORY_GRANT_FEEDBACK — کنترل Row Mode Memory Grant Feedback
• MEMORY_GRANT_FEEDBACK_PERSISTENCE — ماندگاری Memory Grant Feedback
• MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT — Percentile Memory Grant Feedback
• BATCH_MODE_ADAPTIVE_JOINS — کنترل Batch Mode Adaptive Joins
• BATCH_MODE_ON_ROWSTORE — Batch Mode on Rowstore
• TSQL_SCALAR_UDF_INLINING — Scalar UDF Inlining در T-SQL
• INTERLEAVED_EXECUTION_TVF — Interleaved Execution برای Multi-Statement TVF
• DEFERRED_COMPILATION_TV — Deferred Compilation برای Table Variable
• DOP_FEEDBACK — DOP Feedback در SQL Server
• CE_FEEDBACK — Cardinality Estimation Feedback
• OPTIMIZED_PLAN_FORCING — Optimized Plan Forcing
• CLEAR_PROCEDURE_CACHE — پاک‌سازی Procedure Cache در سطح پایگاه داده