forced parameterization sql server, query performance tuning, parameter sniffing fix, plan guide optimization, sql dba tuning, بهینه سازی کوئری, افزایش سرعت sql server, مشکل کندی پایگاه داده, مشاوره performance sql

بهینه‌سازی اجرای کوئری‌ها در SQL Server، بیش از هر چیز به Query Plan وابسته است. Query Optimizer برای هر دستور T-SQL، یک Execution Plan تولید می‌کند. این Plan تعیین می‌کند داده‌ها چگونه خوانده شوند، کدام Index استفاده شود، Joinها چگونه انجام شوند و …
اگر تعداد درخواست‌ها بالا باشد یا تنوع کوئری‌ها زیاد باشد، Plan Compilation می‌تواند به یک گلوگاه تبدیل شود. این‌جاست که ویژگی‌ای به نام:

Forced Parameterization

می‌تواند وارد بازی شود.

این ویژگی به SQL Server می‌گوید:

«به جای ساختن یک Query Plan برای هر کوئری، ساخت Plan را بر اساس الگوهای پارامتری انجام بده و برای مقادیر مختلف از همان Plan استفاده کن.»

در نتیجه:

  • تعداد Compile/Recompile کاهش پیدا می‌کند.
  • فشار CPU کمتر می‌شود.
  • کارایی در سیستم‌های Transactional بالا می‌رود.

اما مثل همیشه، هیچ ویژگی جادویی بدون عوارض نیست.
Forced Parameterization می‌تواند در سناریوهای اشتباه باعث بدتر شدن عملکرد شود.

این مقاله توضیح می‌دهد چه زمانی باید استفاده شود و چه زمانی نباید.

Parameterization چیست؟ 

SQL Server هنگام اجرای هر کوئری دو گام دارد:

مرحلهتوضیح
Compileتولید Query Plan
Executeاجرای Plan روی داده‌ها

اگر دو نسخه از یک کوئری فقط در مقدار پارامتر متفاوت باشند، SQL Server می‌تواند از یک Plan برای هر دو استفاده کند:

SELECT * FROM Sales WHERE CustomerId = 14;
SELECT * FROM Sales WHERE CustomerId = 27;

SQL Server این را به شکل زیر تبدیل می‌کند:

SELECT * FROM Sales WHERE CustomerId = @p1;

به این کار Parameterization گفته می‌شود.

Simple vs Forced Parameterization

حالترفتار SQL Server
Simple (حالت پیش‌فرض)تنها برخی کوئری‌های ساده پارامتری می‌شوند
Forcedتقریباً تمام کوئری‌ها به صورت خودکار پارامتری می‌شوند حتی اگر Complex باشند

فعال‌سازی Forced Parameterization برای کل دیتابیس

ALTER DATABASE MyDB
SET PARAMETERIZATION FORCED;

چرا Forced Parameterization می‌تواند مفید باشد؟

۱) کاهش Plan Cache Pressure

اگر اپلیکیشن به شکل زیر Query تولید ‌کند:

SELECT * FROM Orders WHERE Amount > 100;
SELECT * FROM Orders WHERE Amount > 120;
SELECT * FROM Orders WHERE Amount > 180;

در حالت Simple:

  • برای هر مقدار یک Plan ساخته می‌شود.
  • Plan Cache بزرگ و پراکنده می‌شود.

در حالت Forced:

  • تنها یک Plan ساخته می‌شود.
  • مصرف CPU کاهش می‌یابد.

۲) مناسب برای OLTP با الگوهای تکراری

اگر سیستم شما:

  • تراکنش‌های زیاد
  • کوئری‌های ساده
  • دسترسی‌های تکراری

داشته باشد → Forced Parameterization بسیار کمک می‌کند.

مثال:

CRM, Billing System, POS, Ticketing Systems

اما کجا مشکل ایجاد می‌کند؟

۱) Parameter Sniffing Problem تشدید می‌شود.

اگر توزیع داده‌ها غیر یکنواخت باشد، ممکن است یک مقدار پارامتر باعث تولید Plan بسیار خاص شود و همان Plan برای مقادیر دیگر ضعیف باشد.

مثال بد:

SELECT * FROM Transactions WHERE BranchId = @p1;

اگر BranchId = 1 شامل ۱ میلیون رکورد باشد.
ولی BranchId = 20 فقط ۲۰۰ رکورد داشته باشد.
استفاده از یک Plan برای همه‌ی Branchها = فاجعه عملکردی

۲) کوئری‌های پیچیده مناسب این روش نیستند.

Queries با:

  • Joinهای چندلایه
  • Subqueryهای وابسته
  • فیلترهای Selective

بهتر است Plan مستقل داشته باشند.

مقایسه دقیق مزایا و چالش‌ها

مزایامعایب
کاهش Compilations و CPU Loadافزایش احتمال Parameter Sniffing
کوچک‌تر شدن Plan Cacheکاهش Precision در Planهای بهینه
مناسب برای OLTP با ترافیک بالانامناسب برای Queryهای Analytics یا Report محور
بهبود Response Time در سیستم‌های Real-timeممکن است باعث Slow Query های تصادفی شود.

یک رویکرد حرفه‌ای: Forced Parameterization Selective

راهکار:

Forced را فعال نکنید،
بلکه فقط برای کوئری‌های خاص اجباری کنید.

EXEC sp_create_plan_guide 
    @name = N'ForceParam_Guide1',
    @stmt = N'SELECT * FROM Orders WHERE CustomerId = @id',
    @type = N'SQL',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

این روش برای سیستم‌های جدی تولیدی توصیه می‌شود.

بهترین سناریوهای پیشنهادی

قابل استفاده:

  • سیستم OLTP است.
  • کوئری‌ها ساختار یکسان دارند.
  • تنوع پارامتر زیاد اما الگوی Query ثابت است.

بهتر است استفاده نکنید:

  • سیستم تحلیلی یا گزارش‌گیری است.
  • داده‌ها Skewed / ناهمگن هستند.
  • Planهای Query نیاز به Optimal Execution دارند.

Monitoring (چگونه بفهمیم Effect چیست؟)

نمایش حجم Plan Cache

SELECT SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS PlanCacheMB
FROM sys.dm_exec_cached_plans;

شمارش Compile Events

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%compil%';

اگر پس از Forced:

  • Compile/sec کاهش → خوب است
  • Avg CPU/Query افزایش → بد است → برگردید
سوالات متداول (FAQ)
پرسشپاسخ
آیا Forced Parameterization روی TempDB تأثیر دارد؟بله، باعث کاهش رشد Plan Cache و مصرف TempDB می‌شود.
آیا باید روی همه دیتابیس‌ها فعال شود؟خیر — توصیه می‌شود Selective Plan Guide استفاده شود.
راه‌حل مشکل Parameter Sniffing چیست؟OPTIMIZE FOR UNKNOWN یا Recompile هدفمند.
تماس و مشاوره با لاندا

اگر سیستم شما:

  • گاهی کند می‌شود.
  • CPU در ساعات اوج بالا می‌رود.
  • Query Planها بی‌ثبات هستند.

در لاندا  یک سرویس Performance Review شامل:

  • تحلیل Planهای واقعی
  • بررسی Skew داده
  • تصمیم‌گیری علمی درباره Forced vs Simple
  • اجرای Plan Guides بدون Downtime

داریم برای مشاوره رایگان با کارشناسان لاندا تماس ✆ بگیرید.

روزبه امیرعصامی

SQL Server

SQL Serverاسکیوال سروربهینه سازی کوئری

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *