بهینه‌سازی طرح‌های حساس به پارامتر در SQL Server 2022: افزایش کارایی کوئری‌ها(Parameter Sniffing)
SQL Server 2022 با معرفی ویژگی **بهینه‌سازی طرح‌های حساس به پارامتر** (Parameter Sensitive Plan Optimization یا PSP)، گام بزرگی در حل یکی از چالش‌های دیرینه کارایی کوئری‌ها برداشته است. این نوآوری به طور چشمگیری به کاهش مشکلات ناشی از “پارامتر اسنیفینگ” (Parameter Sniffing) کمک می‌کند و عملکرد پایگاه داده را برای کوئری‌های پارامتری بهبود می‌بخشد. برای مدیران پایگاه داده و توسعه‌دهندگان، درک این قابلیت جدید برای افزایش کارایی سیستم و ارائه تجربه بهتر به کاربران ضروری است.
یکی از متداول‌ترین مشکلات عملکردی در SQL Server، پدیده **پارامتر اسنیفینگ** است. این مشکل زمانی رخ می‌دهد که SQL Server برای اولین باری که یک رویه ذخیره‌شده (Stored Procedure) یا یک کوئری پارامتری اجرا می‌شود، یک طرح اجرایی (Execution Plan) ایجاد و کش می‌کند. این طرح بر اساس مقادیر پارامترهای اولین اجرای کوئری بهینه می‌شود. اگرچه این رویکرد برای بسیاری از سناریوها کارآمد است، اما زمانی که توزیع داده‌ها (Data Distribution) در جداول یکنواخت نباشد و مقادیر پارامترها در اجراهای بعدی به طور چشمگیری متفاوت باشند، مشکل‌ساز می‌شود. به عنوان مثال، اگر اولین اجرای کوئری با پارامتری انجام شود که تعداد کمی سطر را برمی‌گرداند (مثلاً یک مشتری خاص)، طرح اجرایی ممکن است از یک ایندکس جستجو (Index Seek) استفاده کند. اما اگر اجرای بعدی با پارامتری باشد که میلیون‌ها سطر را برمی‌گرداند (مثلاً تمام سفارشات در یک تاریخ پرفروش)، همان طرح اجرایی به شدت ناکارآمد خواهد بود، زیرا ممکن است اسکن ایندکس (Index Scan) یا اسکن جدول (Table Scan) گزینه بهتری باشد.
SQL Server 2022 با معرفی بهینه‌سازی PSP این مشکل را حل می‌کند. این ویژگی به Query Optimizer امکان می‌دهد تا چندین طرح اجرایی (plan variants) برای یک کوئری پارامتری واحد را در Query Store ذخیره کند. هر یک از این طرح‌ها برای طیف خاصی از مقادیر پارامترها بهینه شده است. به این ترتیب، در زمان اجرا، SQL Server می‌تواند بر اساس مقادیر واقعی پارامترهای ارائه‌شده، بهترین طرح اجرایی را انتخاب کند و از کارایی بهینه اطمینان حاصل کند.
این فرآیند به این صورت کار می‌کند که Query Store نه تنها آمارهای اجرایی را برای یک کوئری و طرح واحد جمع‌آوری می‌کند، بلکه اکنون می‌تواند چندین طرح و آمارهای مربوط به آن‌ها را برای یک کوئری واحد ذخیره کند. هنگامی که یک انحراف قابل توجه در تخمین کاردینالیتی (Cardinality Estimation) بین طرح کش‌شده فعلی و مقادیر واقعی پارامتر مشاهده شود، یک طرح اجرایی جدید (variant) تولید و ذخیره می‌شود. سپس، در اجراهای بعدی، SQL Server به طور پویا ارزیابی می‌کند که کدام یک از طرح‌های موجود برای مقادیر فعلی پارامتر مناسب‌تر است و آن طرح را انتخاب می‌کند. این کار بدون نیاز به Recompile کامل کوئری در هر بار اجرا انجام می‌شود.
فعال‌سازی این قابلیت بسیار ساده است و از طریق تنظیمات Database Scoped Configuration صورت می‌گیرد. این تنظیم می‌تواند در سطح پایگاه داده فعال یا غیرفعال شود:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;همچنین می‌توانید آن را غیرفعال کنید:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;و یا از حالت پیش‌فرض `AUTO` استفاده کنید که در SQL Server 2022 فعال است.
برای مشاهده و نظارت بر این طرح‌های اجرایی مختلف و کارایی آن‌ها، می‌توان از نماهای مدیریت پویا (Dynamic Management Views یا DMVs) استفاده کرد. DMV `sys.query_store_plan_variants` اطلاعات مربوط به طرح‌های مختلف تولید شده برای یک کوئری را نشان می‌دهد، از جمله مقادیر پارامترهای استفاده شده برای هر طرح و دلیل ایجاد طرح جدید. همچنین می‌توان از `sys.query_store_runtime_stats` برای مشاهده آمارهای اجرایی هر یک از این طرح‌های متفاوت استفاده کرد.
مثالی از کوئری برای بررسی طرح‌های حساس به پارامتر در Query Store:
SELECT
q.query_id,
q.query_text,
p.plan_id,
qv.variant_id,
qv.predicate_parameter_values,
qv.recompile_reason_desc,
rs.avg_duration,
rs.avg_cpu_time
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_plan_variants AS qv
ON p.plan_id = qv.plan_id
JOIN sys.query_store_runtime_stats AS rs
ON qv.query_id = rs.query_id AND qv.plan_id = rs.plan_id
WHERE q.query_text LIKE N'%YourQueryText%'
ORDER BY q.query_id, qv.variant_id;
**ملاحظات و محدودیت‌ها:**
- این ویژگی به فعال بودن **Query Store** برای پایگاه داده نیازمند است. اگر Query Store فعال نباشد، PSP Optimization عمل نخواهد کرد.
- استفاده از چندین طرح اجرایی به معنای افزایش حجم داده‌های ذخیره‌شده در Query Store است. این امر می‌تواند منجر به مصرف بیشتر فضای دیسک شود، بنابراین مدیریت اندازه Query Store اهمیت پیدا می‌کند.
- PSP Optimization تنها برای کوئری‌های پارامتری که با استفاده از `sp_executesql` یا رویه‌های ذخیره‌شده اجرا می‌شوند، کاربرد دارد. کوئری‌هایی که پارامتری نیستند یا از هینت‌هایی مانند `OPTION (RECOMPILE)` استفاده می‌کنند، از این قابلیت بهره‌مند نخواهند شد.
OPTION (RECOMPILE)
یا
OPTION (USE HINT (…))
اگر یک کوئری حاوی هینت‌هایی باشد که کامپایل مجدد را اجباری می‌کنند، بهینه‌سازی PSP اعمال نمی‌شود، زیرا هدف این هینت‌ها دقیقا تولید یک طرح جدید برای هر اجرا است.
در نهایت، بهینه‌سازی طرح‌های حساس به پارامتر در SQL Server 2022 ابزاری قدرتمند برای افزایش کارایی و پایداری سیستم‌های پایگاه داده است. با درک نحوه عملکرد و نظارت صحیح بر آن، می‌توان از مزایای کامل این قابلیت بهره‌مند شد و به طور موثر مشکلات عملکردی ناشی از پارامتر اسنیفینگ را مدیریت کرد. این ویژگی نقش مهمی در آینده بهینه‌سازی کوئری در SQL Server ایفا خواهد کرد.