Two days back one of my colleque came to me and started complaining about the performance of an Stored Procedure. Her complaint was one stored procedure is taking long time from the application but completing in two seconds in SSMS.I told her it might be due to parameter sniffing but she was not ready to accept that because store procedure is running using the same parameter in the application and SSMS. Let us try to understand the parameter sniffing and various solution for parameter sniffing.
Understand the parameter sniffing
SQL Server use the statistics to estimate the cardinality of a predicate(where condition) and use that information to produce an optimal execution plan for the current parameter.This execution plan will be cached in the procedure cache and used for subsequent execution of the same parameterized query even with different parameter values.This behavior is called parameter sniffing and it is not bad always. This help us to save the optimization time as the query does not need to be compiled again. The problem occurs when the plan generated for the first execution is not optimal for the subsequent execution.For example let us assume that we have stored procedure which returns the details of the customer based on the input range.In the first execution we have passed parameters as 1 and 5000 and the execution plan might perform an index scan on the customer table. In the next execution of the same procedure will use the same plan even if you passed parameters 1 and 10.The optimal plan might be a index seek and the table scan due to the parameter sniffing will cause for delay in response and excess usage of resources. Many people have a misconception that parameter sniffing will happen only with stored procedure but it can happen with parameterized query also.
Let us see execution plan of the below two statements
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN 11000 AND 30118
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN 11000 AND 11002
The first query is done withe clustered index scan and the second one is done with index seek with key look up operation. These are the optimal execution plan. Let us see how it will work if these statement are converted into a procedure/parameterized query.
EXEC sp_executesql N'SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID between @Start and @End;',N'@Start INT,@End INT',@Start=11000,@End=30118
EXEC sp_executesql N'SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID between @Start and @End;',N'@Start INT,@End INT',@Start=11000,@End=11002
Surprisingly the execution plans of both statements are same! Right click on the left most of operator of the second execution plan and select properties.we can see the below information.
We can see that the parameter compiled values are same as the parameter of the first statement. You can see the same information in the XML execution plan also as given below.
As we discusses the earlier, query optimization and plan generation are costly operations.To avoid this cost , SQL server will try to keep the generated execution plans in plan cache and reuse the same in subsequent execution.However, It is not possible to use the same plan , if the connection has different SET options.It will force to generate new plan instead of using the cached plan and this plan will be reused by all connection having the same SET options.The following SET options are plan-reuse-affecting:
The above SET options are plan-reuse-affecting because SQL Server performs "constant folding" (evaluating a constant expression at compile time to enable some optimizations) and because settings of these options affects the results of such expressions.Let us see an example.
CREATE PROCEDURE Get_SalesOrderHeader (
@ToCustid INT ) AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid
Run this procedure from one of the SSMS window
SET ARITHABORT OFF
Run below query to find out the plan details which will return one row with usecounts value 1
SELECT OBJECT_NAME(CONVERT(INT,PvtAttr.objectid)),plan_handle, usecounts, PvtAttr.set_optionsFROM (
SELECT plan_handle, usecounts,f.value,f.attribute
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) f
WHERE cacheobjtype = 'Compiled Plan') AS PlanAttr
PIVOT (MAX(PlanAttr.value) FOR PlanAttr.attribute IN ("set_options" "objectid") AS PvtAttrWHERE PvtAttr.objectid = OBJECT_ID('dbo.get_SalesOrderHeader')
Possible Solutions for Parameter Sniffing
If you feel that parameter sniffing affecting the performance of a procedure, you have a few choices.
Optimize for a specific parameter
This can be used , if you feel that most of the execution of the procedure can be benefited form execution plan of a specific parameters. For example in our case , let us assume that the procedure is going return maximum of two or three customers in most of the execution. Then you can create procedure as given below
ALTER PROCEDURE Get_SalesOrderHeader (
@FromCustid INT, @ToCustid INT ) AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid OPTION (OPTIMIZE FOR (@FromCustid = 11000,@ToCustid = 11002))
Recompile for all executionThis option will force to recompile the procedure for every execution and generate optimal plan for the current parameter. It has a overhead of recompilation .If the procedure has many statements, the cost of recompilation will increase.This can be achieved in two ways.
ALTER PROCEDURE Get_SalesOrderHeader
( @FromCustid INT, @ToCustid INT )
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid OPTION (RECOMPILE)
ALTER PROCEDURE Get_SalesOrderHeader
( @FromCustid INT, @ToCustid INT ) WITH RECOMPILE
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid
An interesting difference between two of this approach is : WITH RECOMPILE , the plan is never put into the plan cache, whereas this happens with OPTION (RECOMPILE) and it use the plan cache in following situations.
The procedure is called with a very high frequency, and the compilation overhead hurts the system.
The query is very complex and the compilation time has a noticeable negative impact on the response time.
Local Variable or option for unknown
The traditional way of handling the parameter sniffing is assigning the parameter value to a local variables and use the local variable. The option for unknown also works in the same way. When the query optimizer knows the parameter value, it can use the statistical histogram to estimate the number of records that can be returned by the query and can generate the best plan based on the histogram of the statistics .When the parameters values are assigned to the local variable , optimizer can not use histogram instead it uses the density vector of the statistics. I will explain this in detail in my my next post.This can be implemented as given below:
ALTER PROCEDURE Get_SalesOrderHeader ( @FromCustid INT, @ToCustid INT ) AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid OPTION (OPTIMIZE FOR UNKNOWN)
ALTER PROCEDURE Get_SalesOrderHeader ( @FromCustid INT, @ToCustid INT )AS
DECLARE @L_FromCustid INT
DECLARE @L_ToCustid INT
SET @L_FromCustid =@FromCustidSET @L_ToCustid =@ToCustid SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @L_FromCustid AND @L_ToCustid
Apart from this we can implement a decision tree procedure where it will call two separate procedure based on difference between two parameter value.For example one for fetching less than 50 customer and other one for fetching more than 50 customers.
Thank you for reading this post.If you liked this post, do like my page on Facebook : http://www.facebook.com/practicalSqlDba
Thanks for sharing!!ReplyDelete
very nice article, thanks for sharing!ReplyDelete
@ Nelson ,ReplyDelete
I want use set ARITHABORT on in view for critical requirement.
how can it be possible or suggest me alternate way to solve this Problem.
It is very urgent .Waiting for Reply.
Awesome post, thank you for sharing!ReplyDelete
That's great, thanks. Is it possible that you get different results due to parameter sniffing? Or does it just affect performance?ReplyDelete
you will not get different result due to parameter sniffing ...it is all about performance.Delete
Here is an ORM that works with SQL Server
But Why? Is this sql server bug?ReplyDelete
How i Can use SET ARITHABORT OFF in ADO.NET ???ReplyDelete
Nice Article !ReplyDelete
I have also worked around this and created one detailed post on Parameter Sniffing.
Please visit this url:
I loved the way you discuss the topic great work thanks for the share, Let me share this, dotnet training in pune
Thanking now we this blog title and information was really good. I will love that's our content.This blog title explains for remove that's the stress and enjoyable working and then understanding the job life.ReplyDelete
Selenium Training | Selenium Online Training | Selenium Training Institute
Hi, We update this blog content is really well.I really easy understand our blog. Very interesting going on this blog.I like this blog concept.ReplyDelete
SEO Training in Chennai
Best SEO Training in Chennai
SEO Training Institute in Chennai
شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
شركة نقل اثاث بالدمام
شركة نقل اثاث بالجبيل
شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمامReplyDelete
شركة نقل عفش بالدمام
نقل عفش بالخبر
شركة نقل اثاث الدمام
نقل عفش الدمام
نقل عفش بالدمام
ان اردت نقل عفش منزلك بالدمام ابيات الشرقية من اهم شركات نقل العفش بالدمام والخبر والجبيل والقطيف والاحساء
http://obat-aborsi99.com/ Obat Penggugur Kandungan JaninReplyDelete
http://klinikfarma.com/ Jual Obat Aborsi Alsi
http://situs-online.com/ Jual Obat Aborsi Penggugur Kandungan
http://obataborsi-ampuh.com/ Jual Obat Aborsi Cytotec
Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.ReplyDelete
Hey Very Nice tutorial....I just loved your writing.....Nice blog....keep posting....ReplyDelete
Thanks@Selenium Online Training
Wow! That's really great information guys.I know lot of new things here. Really great contribution.Thank you ...ReplyDelete
thanks for sharing 082210743003ReplyDelete
obat telat bulan
jual obat aborsi
obat aborsi termurah
I have more information to learn in your post. This is very interesting and attractive post. I want more ideas to your blog...ReplyDelete
SEO Course in Aminjikarai
SEO Training in Vadapalani
SEO Course in Chennai
SEO Training in Kelambakkam
SEO Course in Karappakkam
SEO Training in Padur
Tired of boring work? Play and win with BGAOC top online casino come in now and take your winnings.ReplyDelete
The content you have given is Extra-Ordinary. Very interesting write-up. Looking forward for your next Post.ReplyDelete
Ethical Hacking Course in Chennai
Hacking Course in Chennai
Learn Ethical Hacking
Ethical Hacking Training Institute in Chennai
Ethical Hacking Course in Anna Nagar
Node JS Training in Chennai
Node JS Course in Chennai
Photoshop Classes in Chennai
Photoshop Course in Chennai
Я бы рекомендовал использовать профиль для светодиодных лент это нужно для того что бы лента дольше служила.ReplyDelete
All good. I am a newcomer, so I jerked until the money was withdrawn. I waited 2 days on Yandex. Thanks to the admins and support service for listening to my whining on the forum and in the chat. Play people! Do not be greedy. All the rules with this casino perfect online gambling sites real money I often spend my time hereReplyDelete
Jika anda main ada adanya saja, maka itu akan merugikan dan menyulitkan anda untuk bisa menang.ReplyDelete
freebet tanpa deposit
paito warna terlengkap
Please refer below if you are looking for Online Job Support and Proxy support from IndiaReplyDelete
Java Online Job Support and Proxy support from India | AWS Online Job Support and Proxy Support From India | Python Online Job Support and Proxy Support From India | Angular Online Job Support from India | Android Online Job Support and Proxy Support from India| SAP MM Online Job Support and Proxy support from India | ETL Testing Online Job Support and Proxy Support From India | SAP SD Online Job Support and Proxy Support From India
Thank you for excellent article.
beberapa mesin dapat memberi Anda hingga delapan kredit untuk mendapatkan hanya satu taruhan kredit jika Anda mendapatkan rumah penuh. Jelas, Anda dapat menemukan banyak lainnya yang hanya akan memberi Anda tujuh atau delapan, ditambah beberapa bahkan lebih sedikit dibandingkan dengan 98totoReplyDelete
Ireland is considered to be a developed nation. The developmental stage of a nation is determined by a number of factors including, but not limited to, economic prosperity, life expectancy, income equality, and quality of life. http://www.confiduss.com/en/jurisdictions/ireland/economy/ReplyDelete