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))

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

Optimize for unknown

مطالب مشابه

#

#