April 3, 2008 at 7:58 am
I'm having a very weird issue that I'm hoping someone can clarify. We had a stored proc that when you run it through SSMS it returns very quickly. The SP has several parameters that can be passed to it. When you run the SP using .net (either SSRS or a simple asp.net page) using the same parameters I used in SSMS, it took 2.5 minutes. Now my question is this. Is there a different execution plan for different types of connections? As a final test, I used a pass through query in Access and it returned data just as fast as SSMS (about 2 seconds).
To add to the confusion, when I recreated the SP as a new one with a different name, the query now returns data very quickly no matter how the connection is made.
Any thoughts on this?
J.D.
April 3, 2008 at 8:09 am
Sounds like you may have "parameter sniffing" going on. The following quote is from this thread: http://www.sqlservercentral.com/Forums/Topic226815-8-1.aspx
Snippit from this link
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EGAA
Compilations, Recompilations, and Parameter Sniffing
"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation. Both in SQL Server 2000 and SQL Server 2005, parameter values are sniffed during compilation or recompilation for the following types of batches:
• Stored procedures
• Queries submitted via sp_executesql
• Prepared queries
In SQL Server 2005, the behavior is extended for queries submitted using the OPTION(RECOMPILE) query hint. For such a query (could be SELECT, INSERT, UPDATE, or DELETE), both the parameter values and the current values of local variables are sniffed. The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the OPTION(RECOMPILE) hint. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2008 at 8:19 am
The parameter sniffing is pretty likely. You might also have different ANSI settings from your different connections. That's worth checking as well.
"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
April 3, 2008 at 8:41 am
I'll take a look at the link provided. But what still has me stumped is when I ran a trace, the SP call is identical when compared the query run using SSMS.
April 3, 2008 at 9:28 am
J.D. Gonzalez (4/3/2008)
I'll take a look at the link provided. But what still has me stumped is when I ran a trace, the SP call is identical when compared the query run using SSMS.
Are you sending in the exact parameter values? The issue with parameter sniffing is that, what is the best execution plan for 1 set of values may not be the best plan for another set of values. Thus, re-using the plan takes longer than creating a new plan for the new parameters.
In Profiler are you checking for the CacheHit, CacheMiss, and CacheInsert events?
In the app are you using a a commandtype of SP with parameters or a commandtype of text? This will make a difference.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2008 at 9:43 am
The app is using a commandtype of SP. SSRS is also using a commandtype of SP. I was sending the exact parameters as I using on SSMS. In fact, I copied the SP from profiler that the app generated and ran the query in SSMS. It took 2 seconds.
I'll look at the events that mentioned. I suspect that my answer will lie in how it used caching. So assuming that it is using a different cached plan. Other than dropping and recreating the SP, is there a fix?
Thanks for your assistance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply