March 8, 2011 at 3:32 pm
I have a strong feeling we have parameter sniffing going on. If i take the proc taking most time, first time i run takes 20 secs second time takes 2 secs. However from the application it takes about 15 secs sometimes faster. I looked at all possible indexes. I did go through the articles. I wanted to know is there a way i can identify/nail down pararmeter sniffing? Like a counter inside trace or something? Please help !!
March 8, 2011 at 3:49 pm
March 8, 2011 at 9:45 pm
Is this the same to what I had replied here ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157892#617864
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 8, 2011 at 10:48 pm
Oops..I wanted to post the reply for the below thread
http://www.sqlservercentral.com/Forums/Topic1074846-1550-1.aspx
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 9, 2011 at 8:05 am
In general, the way you can tell something is parameter sniffing is not simply if it runs faster or slower, that could be contention or any number of other things. One of the primary indicators is that you get a different execution plan for different parameter values. If you haven't looked at the execution plans, that's where I'd start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 10:26 am
Grant Fritchey (3/9/2011)
In general, the way you can tell something is parameter sniffing is not simply if it runs faster or slower, that could be contention or any number of other things. One of the primary indicators is that you get a different execution plan for different parameter values. If you haven't looked at the execution plans, that's where I'd start.
Thanks.If i am not wrong, if there is parameter sniffing going on there "would" be different execution plans only when the procs are executed from the front end obviously because the same task will be executed repeatedly for more than 100 different users. If i run the sql which runs behind the scene's i get the same execution plan even after repeated executions for two different users. So my question was , how can i identify parameter sniffing on a OLTP database since running the same query from management studio will not really help me.
March 9, 2011 at 10:50 am
sqldba_icon (3/9/2011)
Grant Fritchey (3/9/2011)
In general, the way you can tell something is parameter sniffing is not simply if it runs faster or slower, that could be contention or any number of other things. One of the primary indicators is that you get a different execution plan for different parameter values. If you haven't looked at the execution plans, that's where I'd start.Thanks.If i am not wrong, if there is parameter sniffing going on there "would" be different execution plans only when the procs are executed from the front end obviously because the same task will be executed repeatedly for more than 100 different users. If i run the sql which runs behind the scene's i get the same execution plan even after repeated executions for two different users. So my question was , how can i identify parameter sniffing on a OLTP database since running the same query from management studio will not really help me.
Parameter sniffing occurs all the time with stored procedures or parameterized queries, regardless of where the calls come from. If the call to a procedure was made form the front-end or SSMS, parameters in that call were sampled, or "sniffed," as part of the process of creating the execution plan. So the difference in execution plans occurs not because of where it's run from (although, different connection settings can cause a completely different execution plan to be created), but because of differences in the parameters that are sampled by SQL Server result in radically different plans.
Just looking at slow or fast performance doesn't tell you anything. You have to look at the execution plan when it's slow and see what values were used to compile the plan and then look at it when it's fast and see what values were used to compile that plan. then you look at the statistics to see if either of these different values (assuming they are different) would result in changes to the execution plan.
Another thing to check, are your stats up to date. Do you have statistics maintenance in place?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2011 at 9:59 am
First-execution times being slow is almost certainly due to simply reading data from disk. After that you are hitting data directly in RAM, which is way faster.
Issues with parameter sniffing show up when one user calls sproc with something like @start = 2011-01-01, @end = 2011-01-01 and you get a nice, efficient index seek/bookmark lookup/nested loop joining type of plan for the few rows that come back from the 100M row table. That plan is cached. The next users calls same sproc with @start = 1999-01-01, @end = 2011-12-31. The index seek/bookmark lookup/nested loop joining cached plan is now DISASTEROUSLY bad for hitting all 100M rows of data. The best thing to do for this type of issue is a simple OPTION (RECOMPILE) on the statement(s) in the sproc. Magic bullet fix.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 10, 2011 at 11:36 am
TheSQLGuru (3/10/2011)
First-execution times being slow is almost certainly due to simply reading data from disk. After that you are hitting data directly in RAM, which is way faster.Issues with parameter sniffing show up when one user calls sproc with something like @start = 2011-01-01, @end = 2011-01-01 and you get a nice, efficient index seek/bookmark lookup/nested loop joining type of plan for the few rows that come back from the 100M row table. That plan is cached. The next users calls same sproc with @start = 1999-01-01, @end = 2011-12-31. The index seek/bookmark lookup/nested loop joining cached plan is now DISASTEROUSLY bad for hitting all 100M rows of data. The best thing to do for this type of issue is a simple OPTION (RECOMPILE) on the statement(s) in the sproc. Magic bullet fix.
i agree, but you mentioned in your first line that "first execution is slow" which is true in my case, so i dont think option(recompile) is an option for me
March 10, 2011 at 12:13 pm
sqldba_icon (3/10/2011)
TheSQLGuru (3/10/2011)
First-execution times being slow is almost certainly due to simply reading data from disk. After that you are hitting data directly in RAM, which is way faster.Issues with parameter sniffing show up when one user calls sproc with something like @start = 2011-01-01, @end = 2011-01-01 and you get a nice, efficient index seek/bookmark lookup/nested loop joining type of plan for the few rows that come back from the 100M row table. That plan is cached. The next users calls same sproc with @start = 1999-01-01, @end = 2011-12-31. The index seek/bookmark lookup/nested loop joining cached plan is now DISASTEROUSLY bad for hitting all 100M rows of data. The best thing to do for this type of issue is a simple OPTION (RECOMPILE) on the statement(s) in the sproc. Magic bullet fix.
i agree, but you mentioned in your first line that "first execution is slow" which is true in my case, so i dont think option(recompile) is an option for me
But you were also inquiring about parameter sniffing so I gave you what in my experience is by far the most frequent cause of plan cache performance problems in case it fit your scenario.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 10, 2011 at 12:38 pm
I found this article the other day which I found helpful on a number of levels - it may be quite useful to you here.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply