Optimize for unknown

مفهومي در SQL Server وجود دارد به نام parameter sniffing كه شرح آن به صورت زير است.
ابتدا رويه ذخيره شده زير را در نظر بگيريد:

create procedure test (@pid int)
as
select * from Sales.SalesOrderDetail
where ProductID = @pid

استفاده از كوئري‌هاي پارامتري يكي از بهترين تمرين‌هاي كاري با SQL server است؛ از آنجائيكه در اين حالت plan تهيه شده مجددا مورد استفاده قرار گرفته، همچنين از SQL injection نيز جلوگيري خواهد كرد، زيرا براي نمونه در مثال فوق تنها pid از نوع int پذيرفته مي‌شود و نه هر ورودي خطرناك ديگري.
اما اين نوع كوئري‌ها يك مشكل را نيز به همراه خود دارند. اين plan تهيه شده به ازاي اولين ورودي رويه ذخيره شده تهيه مي‌شود (parameter sniffing) و الزامي ندارد كه براي دومين ورودي و فراخواني‌هاي بعدي، بهترين plan باشد.

براي حل اين مشكل راه‌هاي زيادي هست:
الف) انتساب پارامترهاي يك رويه ذخيره شده به متغيري محلي

create procedure test (@pid int)
as
Declare @mpid int
Set @mpid = @pid
select * from Sales.SalesOrderDetail
where ProductID = @mpid

در اينجا پارامتر ورودي مستقيما در كوئري استفاده نشده است و SQL Server اين متغير محلي را sniff نخواهد كرد.

ب) استفاده از گزينه RECOMPILE كه سبب خواهد شد به ازاي هر ورودي يك plan بهينه تهيه شود. اين مورد مصرف CPU بالايي را به همراه خواهد داشت.

ج) راه حل ارائه شده در SQL Server 2005
استفاده از روش الف به علاوه اضافه كردن گزينه كمكي زير به انتهاي اسكريپت فوق

OPTION (OPTIMIZE FOR(@pid = 544))

در اينجا فرض بر اين است كه مي‌دانيم pid=544 بسيار مورد استفاده قرار خواهد گرفت، بنابراين اين معرفي را به موتور بهينه ساز SQL Server ارائه خواهيم كرد.

د) راه حل ارائه شده در SQL Server 2008
با استفاده از Optimize for unknown كه در اس كيوال سرور 2008 معرفي شده است، مزيت استفاده از كوئري‌هاي پارامتري همانند استفاده مجدد از plan تهيه شده، حفظ گشته اما اين plan‌ تهيه شده اوليه بر اساس اولين مقدار پاس شده، تهيه نگرديده و حالت عمومي‌تر و بهينه‌تري را براي اكثر مقادير پاس شده خواهد داشت.

create procedure test (@pid int)
as
select * from Sales.SalesOrderDetail
where ProductID = @pid

OPTION(OPTIMIZE FOR (@pid UNKNOWN))

جهت مطالعه بيشتر (+ و + و +)