زمانی که یک Stored Procedure فراخوانی میشه، SQL Server به دنبال Execution Plan مربوط به اون در حافظه Cache می گرده، اگه اونو پیدا کرد بلافاصله اجراش می کنه اما اگه Planی براش پیدا نکرد، باید تمام مراحل ساخت Execution Plan طی بشه.
دیتابیس SQL Server با استفاده از پارامترهایی که اولین بار به یک SP فرستاده میشه، یک Plan ایجاد کرده و اونو Cache میکنه، یعنی یکبار پلن رو می‌سازه تا چندین بار ازش استفاده کنه و در زمان صرفه جویی کنه.
به این پدیده Parameter Sniffing گفته میشه.
در اکثر موافق Cache شدن Plan خوبه اما بعضی وقتا باعث خرابکاری میشه.
نخست SP زیر رو در نظر بگیرید:
حالا SP رو با دو مقدار مختلف فراخوانی کرده و Execution Plan ش رو بررسی می کنیم.
نمونه اول با Customerid = 1060
نمونه اول با Customerid = 90
همون طور که می بینیم برای هر دو مقدار، کوئری پلن یکسان مورد استفاده قرار گرفته اما Estimated Row کوئری دوم با مقادیر نادرست نمایش داده میشه.
شاید زمان اجرای هر دو کوئری مناسب باشه اما مشکل زمانی حادتر میشه که کوئری پلن اولی که Cache شده، برای پارمترهای بعدی، یک انتخاب بد باشه و باعث کندی محسوس در دریافت خروجی بشه.
اگر Properties عملگر Select کوئری پلن دوم رو ببینیم متوجه میشیم که مقدار Compiled Value برابر با مقدار اولین Parameter ی هست که پلن باهاش Cache شده و این مقدار با Runtime Value متفاوته.
برای اینکه جلوی این اتفاق گرفته بشه می تونیم SPرو با Local Variable بازنویسی کنیم، یعنی به شکل:
حالا مجددا کوئری ها رو اجرا می کنیم:
همون طور که مشاهده می کنید Estimated Row برای هر دو کوئری پلن به درستی نمایش داده شده اما در هر صورت دیگه Plan ی وجود نداره که بهش مراجعه و در زمان صرفه جویی شه. استفاده از Local Variable بر مشکل Parameter Sniffing غلبه میکنه اما هیچ تضمینی وجود نداره که بهترین پلن انتخاب شه.
منبع :
