آموزش پیشرفته 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 کلیک کنید.

آموزش پیشرفته SQL Server (جلسه سوم) - آشنایی با Execution Plan

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

Execution Plan چیست؟

Execution Plan به شما کمک می کند تا دریابید:

1. چرا یک Query کند است و زمان اجرای زیادی را مصرف می کند؟
2. آیا SQL SERVER از ایندکس- Index من استفاده می کند یا خیر؟
3. چرا SQL SERVER از ایندکس- Index من استفاده نمی کند؟
4. آیا این پرس و جو - Query از دیگری سریعتر اجرا می شود؟
5. آیا نیاز است که من ایندکس دیگری تعریف کنم و اگر نیاز است روی چه فیلدهایی و چه نوع ایندکسی؟
6. و...

نحوه نمایش  Execution Plan:

برای نمایش Execution Plan باید شما دسترسی لازم برای اجرای Query را روی پایگاه داده داشته باشید، در صورتیکه شما یکی از نقشهای sysadmin, dbcreator یا db_owner را داشته باشید به دسترسی دیگری نیاز ندارید.

برای نمایش Execution Plan می توانید از کلید میانبر ctrl+L با انتخاب Query مورد نظر استفاده نمایید.

انواع Plan:

1. estimated plan: ارزیابی اجرای پرس و جو قبل از اجرا و برآورد وضعیت اجرا را که توسط Optimizer بدست می آید نشان می دهد و به یکی از روشهای زیر قابل دسترسی می باشد:
• با کلیک روی آیکن Display Estimated Execution Plan روی نوار ابزار.
•  با راست کلیلک روی پنجره query و انتخاب same option.
•  با کلیک روی Query option روی نوار menu bar و انتخاب same choice.
•  کلیدهای CTRL+L.
2. actual plan: پلان واقعی اجرای query را نشان می دهد و به یکی از روشهای زیر قابل دسترسی می باشد:
• کلیک روی آیکن Include Actual Execution Plan در نوار ابزار.

• کلیک راست روی پنجرهquery و انتخاب Include Actual Execution Plan از منو.

• انتخاب same option در منوی Query.
• کلیدهای CTRL+M.

برای مثال query زیر را در نظر بگیرید:

برای نمایش Execution Plan باید با دستور زیر امکان نمایش آنها فراهم شود:

-- For Getting the estimated text plan
SET SHOWPLAN_ALL ON;
------------------------------------------
-- For Getting the actual text plan
SET STATISTICS PROFILE ON;

اطلاعات کسب شده از Execution Plan:

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

در ادامه به تعدادی از این موارد اشاره می کنیم و برای آشنایی کامل با Execution Plan پیشنهاد می کنیم جلسه سوم مجموعه افزایش کارآیی و سرعت بانک اطلاعاتی را خریداری نمایید:

• Cached plan size: مقداری از حافظه که توسط این پلان در حین کش پلان مورد استفاده قرار می گیرد.
  
• Degree of Parallelism: تعداد پردازنده ای که توسط این پلان مورد استفاده قرار می گیرد.
• Estimated Operator Cost: هزینه اجرای پرس و جو را ارزیابی می نماید.
• Estimated Subtree Cost: هزینه اجرای این قسمت از query را نسبت به کل آن بیان می کند و از راست به چپ این مراحل دنبال می شوند.
• Estimated Number of Rows: ارزیابی تعداد ردیف انتخاب شده توسط query را نشان می دهد و توسط Optimizer محاسبه می شود.
• Actual Number of Rows: تعداد واقعی ردیف انتخاب شده توسط query را نشان می دهد.

شکل زیر یک نمونه از این ToolTip ها را نشان می دهد:

ترتیب اجرای مراحل در Execution Plan

ترتیب اجرای مراحل از بالا به پایین و از راست به چپ می باشد؛ شکل زیر این ترتیب را بهتر نشان می دهد:

در ادامه بحث آشنایی با Execution Plan خواهید دید...!

1-آشنایی کامل با Execution Plan.

2- آموزش جامع نحوه استفاده از Execution Plan همراه با مثال کاربردی.

3- آموزش تحلیل Execution Plan و نحوه استفاده از آنها در بالا بردن کارایی.

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

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

نقطه شروع بحث بالا (آشنایی با Execution Plan) در ویدئو:  30:50

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