March 17, 2016 at 1:15 pm
We have a stored procedure(SP1) on a database on sQL SERVER 2008 R2 that has three input pararmeters (in1, in2 and in3) recently on application side we started to see timout for two set of input parameter (in1 = 1, in2= 12 in3 = 0) & (in1 = 1, in2= 20 in3 = -1) all the time. when I ran the same stored procedure in SSMS the result set is being returned instantly. But on application side they are failing all the time(for those two set of input pararmeter) I ran profiler and noticed that stored procedures call from application indeed are taking more than 30 sec(time out value has been set to 30 sec) and also they have very high logical reads.
It's interesting when I run the same sp with the same input pararmeters in SSMS. the data is being returned instantly and the logical read is low. this stored procedure is being called constatntly with other input pararmeters and there is no issue for them.
I would appriciate if someone assist me on this.
March 17, 2016 at 3:14 pm
I would start here: http://www.sommarskog.se/query-plan-mysteries.html
March 17, 2016 at 3:19 pm
You post very little details so all I can offer is guesswork.
My guess is that this is caused by parameter sniffing. You have a plan in cache that is reused every time, but that performs badly for that specific set of parameters. When you run the same stored procedure from SSMS, different SET options are in effect, and hence the plan from cache is not reused but a new plan is generated (or another plan, with that combination of SET options) is used.
Use extended events (or SQL Trace - not really preferred but the UI for extended events was nonexisting in SQL2008) to check if a cached plan is used when the application invokes the stored procedure, and to check the execution plan that is used. You will probably see a very low estimated rowcount and a very high actual rowcount.
March 18, 2016 at 2:54 am
I would check the connection settings (things like ANSI_NULLs etc) to see what the differences are, as different settings here can produce different query plans with, as you are seeing, different performance levels.
Easiest way to check is with the profiler, and get it to do the lifting. Martin Bell did a blog post about this a few years ago. http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/15/Other-ways-to-see-User-Options.aspx
edit. Oh, rats. Just what Hugo said. My brain's not awake yet.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply