Parameter Sniffing چيست؟

آموزش پیشرفته SQL Server (جلسه دهم) - Parameter Sniffing چیست؟

بررسی Parameter Sniffing، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Parameter Sniffing چیست؟، قسمت های قبلی را مرور فرمایید.

Parameter Sniffing چیست؟

قبل از اینکه Parameter Sniffing را توضیح دهیم، نحوه اجرا شدن Stored Procedure ها را بررسی می کنیم.

زمانی که یک Stored Procedure فراخوانی می شود، قبل از هر چیز SQL Server بدنبال Execution Plan مربوط به آن در حافظه Cache می گردد و اگر آنرا پیدا کرد بلافاصله آنرا اجرا می کند. اما اگر Execution Plan مربوطه را پیدا نکرد باید تمام مراحل ساخت Execution Plan طی شده و سپس اجرا شود.

بنابراین یکی از بزرگترین محاسن Stored Procedure ها استفاده مجدد از Execution Plan قبلی است.

اما استفاده از Execution Plan قبلی، همیشه بهینه نیست و ممکن است در بعضی شرایط، زمان اجرای Stored Procedure چندین برابر مرحله ی قبل شود ...! این در واقع همان مفهوم Parameter Sniffing است.

اما در ادامه با یک مثال ساده، سعی می کنیم با Parameter Sniffing بیشتر آشنا شویم.

مثال: در مثال زیر 3 کوئری یکسان با سه مقدار متفاوت برای شرط CustomerID داریم:

USE Northwind

1- SELECT OrderID, CustomerID, OrderDate, ShipCountry FROM Orders    
    WHERE CustomerID='CENTC' 

2- SELECT OrderID, CustomerID, OrderDate, ShipCountry FROM Orders    
    WHERE CustomerID='SAVEA' 

3- SELECT OrderID, CustomerID, OrderDate, ShipCountry FROM Orders    
    WHERE CustomerID='BONAP' 

اگر 3 کوئری بالا را در SQL Server اجرا کنید و Execution Plan آنها را بررسی کنید (استفاده از دکمه های میانبر CTR + M) مشاهده خواهید کرد که به ازای کوئری 1 که تنها یک رکورد برگردانده از Index Seek استفاده شده و تنها با استفاده از یک LookUp مقدار برگشتی را نشان می دهد، اما با کمال تعجب در کوئری 2 و 3 که بترتیب 17 و 31 رکورد را برگدانده اند از Index Scan استفاده شده و برای نمایش نتیجه، مجبور شده کل جدول را Scan کند. به هر حال Query Optimizer با توجه به Statisticsها به این نتیجه رسیده که برای کوئری 2 و 3 باید از Index Scan استفاده کند و با توجه به این موضوع Execution Plan را ایجاد کرده است.

اما در ادامه یک Stored Procedure ایجاد می کنیم و کوئری بالا را در آن قرار می دهیم:

CREATE PROCEDURE usp_GetOrders0 (@CustomerID NVARCHAR(10))
AS
    SELECT OrderID, CustomerID, OrderDate, ShipCountry FROM Orders    
        WHERE CustomerID=@CustomerID 
GO

حالا SP بالا را سه مرتبه به ازای سه مقدار قبلی اجرا می کنیم:

1- EXEC usp_GetOrders0 'CENTC'
2- EXEC usp_GetOrders0 'SAVEA'
3- EXEC usp_GetOrders0 'BONAP'

زمانی که برای اولین بار SP اجرا می شود، Execution Plan ساخته می شود و در حافظه Cache قرار می گیرد. بنابراین Execution Plan ای که دارای Index Seek بود حالا Cache شده است. اما در ادامه برای بار 2 و 3 که SP را اجرا می کنیم از Execution Plan قبلی استفاده خواهد شد که اصلاً بهینه نیست ...!

رفع مشکل Parameter Sniffing

برای رفع مشکل Parameter Sniffing روش های مختلفی وجود دارد، در زیر به برخی از آنها اشاره شده است:

1. استفاده از گزینه Recompile
2. استفاده از گزینه Optimize For
3. استفاده از گزینه Optimize For Unknown
4. استفاده از sp_Executesql
5. استفاده از sp_Executesql بهمراه گزینه RECOMPILE
6. استفاده از Local Variable

در ادامه فقط روش آخر که بنظر بنده بهینه ترین روش است توضیح داده خواهد شد.

راه حل مشکل Parameter Sniffing - استفاده از Local Variables

در SP زمانی که از Local Variable استفاده شود بجای استفاده از statistic histograms از statistic densities استفاده خواهد شد.

SP بالا را بصورت زیر تغییر دهید:

CREATE PROCEDURE usp_GetOrders0 (@CustomerID NVARCHAR(10))
AS

DECLARE  @prmCustomerID NVARCHAR(10) = @CustomerID
    SELECT OrderID, CustomerID, OrderDate, ShipCountry FROM Orders    
        WHERE CustomerID=@prmCustomerID 
GO

اشکال روش بالا این است که بخاطر استفاده از statistic densities  بجای statistic histograms ، برای بعضی از کوئری ها از یک Plan نامطلوب استفاده خواهد شد.

در ادامه آموزش Parameter Sniffing چیست؟ خواهید دید ...!

با خرید جلسه 10 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. توضیح جامع و کامل در مورد روش های رفع مشکل Parameter Sniffing
2. در فرم های مربوط به گزارش گیری که فیلدهای فراوانی برای جستجو در اختیار کاربر است، بهترین روش برای نوشتن SP مربوطه کدام است؟
3. چه قابلیتی در SQL Server 2014 به ازای Stored Procedure ها اضافه شده است؟
4. و ...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 10
• نام فایل ویدئو: 02
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Parameter Sniffing چیست؟) در ویدئو: 10:10

برای خرید و دانلود کامل آموزش پیشرفته SQL Server کلیک کنید.

• قبلی
• بعدی