مشکل Parameter Sniffing در SQL Server 2022

مقدمه

چرا اجرای یک Stored Procedure گاهی کند و گاهی سریع است؟ Plan اجرائی یک پروسیجر به ازای اولین مقداری که به پروسیجر پاس داده می شود تشکیل و در اجرای های بعدی مورد استفاده قرار می گیرد. این موضوع می تواند منجر به مشکل Parameter Sniffing شود.

در این مقاله قصد داریم با ایجاد یک مثال به بررسی مشکل Parameter Sniffing بپردازیم.

ابتدا یک Index در جدول Users در دیتابیس StackOverflow بر روی ستون Reputation ایجاد می نمائیم:

Create Index IXNCReputation On dbo.Users (Reputation)

سطح سازگاری دیتابیس را برابر با ۱۵۰ قرار می دهیم:

Alter Database StackOverflow Set Compatibility_Level = 150

و پروسیجر زیر را ایجاد می نمائیم:

Create Procedure USP_GetReputationCount
(@Reputation Int)
As
 Select Count(Reputation) As ReputationCount From dbo.Users
 Where Reputation = @Reputation

همان طور که در تصویر زیر مشاهده می نمائید Plan اجرائی پروسیجر به ازای مقدار ورودی ۲ به صورت سریال است:

اجرا به صورت سریال یعنی SQL Server از یک CPU Core جهت اجرای Procedure استفاده نموده است. زیرا که تعداد رکوردهای پردازشی کم بوده است. به ازای مقدار ورودی ۲ تنها ۹۱۴۹ رکورد پردازش شده است.

این Plan اجرائی که به صورت سریال است در Plan Cache قرار می گیرد و جهت اجراهای بعدی پروسیجر استفاده می شود.

یعنی Plan اجرائی پروسیجر به ازای اولین مقداری که اصطلاحا به پروسیجر پاس داده می شود تشکیل و در اجرای های بعدی مورد استفاده قرار می گیرد.

حالا اگر مقدار یک را به پروسیجر پاس دهیم چه اتفاقی می افتد؟ همان Plan که به شکل سریال است مورد استفاده قرار می گیرد.

تصویر زیر این موضوع را نشان می دهد:

تقریبا ۶ میلیون رکورد در جدول Users وجود دارد که Reputation آن ها برابر با یک است اما SQL Server این حجم از رکورد را تنها با یک CPU Core پردازش می نماید!

چرا که Plan به ازای تقریبا ۹ هزار رکورد تشکیل شده است و در Plan Cache قرار گرفته و از همان Plan در اجراهای بعدی استفاده می شود.

این مشکل، Parameter Sniffing است.

راه های زیادی جهت حل این مشکل وجود دارد که جدید ترین آن ها افزایش سطح سازگاری دیتابیس به ۱۶۰ می باشد.

دستور زیر سطح سازگاری دیتابیس StackOverflow را به ۱۶۰ افزایش می دهد:

Alter Database StackOverflow Set Compatibility_Level = 160

یک بار دیگر پروسیجر را به ازای مقدار ورودی ۲ اجرا می نمائیم، تصویر زیر نشان می دهد که Plan اجرائی پروسیجر به شکل سریال است:

دوباره مقدار یک را به پروسیجر پاس می دهیم، تصویر زیر را مشاهده نمائید:

Plan اجرایی به شکل موازی است. (در این نمونه خطوط زرد رنگ نشان دهنده اجرا به صورت Parallel می باشد) یعنی SQL Server تشخیص داد که به ازای مقدار ورودی ۱ تعداد بسیار زیادی رکورد باید پردازش شود و از چندین CPU Core جهت اجرای پروسیجر استفاده نمود.

سوال مهم

آیا در SQL Server 2022 مشکل Parameter Sniffing کامل برطرف شده است؟

نمونه های زیادی از مشکل Parameter Sniffing وجود دارد، مثال فوق فقط یک نمونه بود. همه نمونه های این مشکل باید با SQL Server 2022 تست شود و مشاهده گردد که، کدام یک از آن ها برطرف شده و چه نمونه هایی از این مشکل هنوز هم وجود دارد.