Pages

Tuesday 26 June 2012

How Local Variable or Optimize for Unknown Resolve Parameter Sniffing

In my last post , we have gone through the parameter sniffing  and possible solutions for parameter sniffing. In the possible solutions except the local variable and Optimize For Unknown are very straight forward solution and we know how they helps us to resolve the issue.In this post we will see how local variable and option for unknown are resolving the parameter sniffing issue.

If the parameter values are known while compiling the stored procedure , the optimizer use the statistics histogram and generate the best plan for the parameters.When we define local variable and use that in the query, SQL server will not be able use the parameter values to find the optimal value. In this scenario optimizer use density vector information of the statistics and it will generate same execution plan for all input parameter. Let us see how it will work.


Below statement returns returns 13 records by doing index seek and key lookup operation.This plan is generated based on the estimation that, the query will return 44.5 records. The query optimizer done the estimation based on the histogram.

SELECT * FROM Sales.SalesOrderDetail WHERE productid =744
DBCC show_statistics('Sales.SalesOrderDetail','IX_SalesOrderDetail_ProductID')


A portion of the out put of the above query will looks like below

The estimated number of rows is calculated based on the EQ_ROWS and AVG_RANGE_ROWS. In this example, the parameter value 744 is not matching with RANGE_HI_KEY and optimizer took AVG_RANGE_ROWS values of 747 to calculate the estimated number of rows. The execution plan will be same if you convert this to a procedure.

Let us see how it will work with procedure with local variable

  CREATE PROCEDURE get_SalesOrderDetail 
(
   @ProductId INT
)AS
   DECLARE
@L_ProductId INT
   SET
@L_ProductId =@ProductId
  
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @L_ProductId 



If you execute this procedure with parameter value 744, the execution plan will will looks like below.
EXEC get_SalesOrderDetail 744


This time optimizer gone for index scan under the estimation that the query will return 456 records.As we have defined the local variable, the parameter value is not available at compilation time and optimizer used the density vector to estimate the number of row. The value of estimated number of rows will be same in execution plan of this procedure with any parameter value and hence the execution plan.


Let us see how optimizer calculating the estimated number of rows in this case. As the parameter value is not available at the time of optimization, it assumes that records are distributed uniformly. In the SalesOrderDetail table we have 266 distinct value for Productid and the total number of records is 121317.If you divide total number of records with number of distinct values of productid , you will get 121317/266=456.07 which is same as estimated number of rows. All these data required for the calculation are available in the statistics.The total number of records is available in the first sections. The density value 0.003759399 is available in the second section which is equivalent to 1/266. So the estimated number of rows =121317X0.003759399 = 456.079.

You can see the same execution plan if we change this procedure with optimize for unknown as given below

ALTER PROCEDURE get_SalesOrderDetail (
  
@ProductId INT)AS
SELECT
* FROM Sales.SalesOrderDetail WHERE ProductID = @ProductId OPTION (OPTIMIZE FOR UNKNOWN)


Hope you enjoyed reading this post.If you liked this post, do like my FaceBook Page: http://www.facebook.com/PracticalSqlDba






3 comments:

  1. نظراً لجميع الخدمات التي تقوم بتقديمها المؤسسة وتوفرها لجميع عملائها ويأتي كل ذلك من اجل الثقة التي وضعها جميع عملائنا لنا، فإذا كنت قادم علي النقل وتغير مقر إقامتك من موضع لموضع أخر أو ترغب في نقل أي أثاث من مقر لموضع أخر فشركة نقل اثاث بخميس مشيط خيارك الأول واختيارك الأجود.
    شركة نقل عفش
    شركة نقل عفش من الرياض الى الاردن
    شركة نقل اثاث من الرياض الى الاردن
    شركة نقل عفش بجازان

    ReplyDelete
  2. لا بد ان تكون خدمات التنظيف ونقل الاثاث في الرياض على مستوى لائق باهل العاصمة ونقدم لكم افضل شركة نقل عفش بالرياض مضمونة وتقدم خدمات رائعة وتستخدم سيارات نقل عفش مخصصة ومبطنة من الداخل وايضا تجد خدمات التنظيف للمنازل والفلل والشقق في ارخص شركة تنظيف فلل بالرياض تمتلك خبرة طويلة في اعمال تنظيف الشقق والفلل والقصور وجلي وتلميع جميع انواع البلاط ونقدم ايضا خدمات تنظيف المنازل بالبخار في الرياض تحت اسم اقوى شركات تنظيف كنب بالرياض آمنة بهدف الحصول على تنظيف منزلي شامل للارضيات والشبابيك والمفروشات كالكنب والمجالس والموكيت والسجاد وقد تحتاج ايضا الى تنظيف خزان المياه خاصتك وذلك بالتعاقد مع افضل شركة تنظيف خزانات بالرياض لخدمات تنظيف وتعقيم وصيانة لخزان الماء خاصتك وعمل تعقيم للخزان الأرضي والعلوي ولا بد ايضا ان تهتم بتنظيف المنزل من الحشرات مع احسن شركة مكافحه حشرات بالرياض مضمونة لتعقيم المنزل او المسجد والتخلص من الحشرات المزعجة

    ReplyDelete
  3. خدمات نقل عفش مع الفك والتركيب تلك هي ارخص شركة نقل عفش بجدة تمتلك امكانيات كبيرة لأعمال نقل العفش في مدينة جدة وما جاورها من مناطق تابعة لها وقد نضطر قبل نقل العفش الى نظافة المنزل الجديد قبل النقل من الداخل ومن الخارج وذلك بالتواصل مع افضل شركات تنظيف منازل بجده متمكنة بأعمال التنظيف للمنازل الجديدة والمنازل المفروشة مثل ارخص شركة تنظيف مجالس بجده تقدم امكانيات غسيل الموكيت والكنب في الموقع لكي يتم تعقيم المنزل ومن الأفضل ان تقوم بعمل مكافحة للحشرات بواسطة افضل شركة مكافحه حشرات بجده التي تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة ونحتاج ايضا الى تنظيف الخزان وذلك بالتعرف على اكبر شركة تنظيف خزانات بجدة تقدم افضل الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا

    ReplyDelete