Slow as stored procedure, fast as T-SQL script in SSMS

Question

Slow as stored procedure, fast as T-SQL script in SSMS

SQL Guy 1

SSCoach

Points: 15979
More actions
March 29, 2016 at 1:47 pm

#399939

I have a stored procedure that if I call it by name with parameters it executes in about 40 sec.
If I script the same stored procedure in SSMS, comment "create ... as ..." and place "declare" above input parameters with the same values, it executes in 1 sec. In either case it returns empty result set.
I run both of them in the same server, same database context, same values for parameters, and I check for blockages during execution. It has 8 statements, and two of them are with temp table. Tried to get an execution plan for them, but it returned an error because of temp table.
What I can check? What is methodology to troubleshoot such cases?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

I would start by reading Kendra Little's article, which is here[/url].

You might use the WITH RECOMPILE hint to force the generation of a new execution plan, but read her article first. And don't just try it on a production server... don't want to go getting people fired.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

SQL Guy 1 (3/29/2016)
I have a stored procedure that if I call it by name with parameters it executes in about 40 sec.
If I script the same stored procedure in SSMS, comment "create ... as ..." and place "declare" above input parameters with the same values, it executes in 1 sec. In either case it returns empty result set.
I run both of them in the same server, same database context, same values for parameters, and I check for blockages during execution. It has 8 statements, and two of them are with temp table. Tried to get an execution plan for them, but it returned an error because of temp table.
What I can check? What is methodology to troubleshoot such cases?
Thanks

This has a distinct odor about that is almost certainly parameter sniffing. Check out this article from Gail about what it is and how to deal with it. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

pietlinden (3/29/2016)
I would start by reading Kendra Little's article, which is here[/url].
You might use the WITH RECOMPILE hint to force the generation of a new execution plan, but read her article first. And don't just try it on a production server... don't want to go getting people fired.

This is another great article on the topic. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 4

No, no, no, I don't do it on production system. It's on a test server that's refreshed nightly from production.

Recreated S.P. with recompile, but the problem is still the same.

Now will read linked articles.

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 5

RECOMPILE might not be the right hint to use here. If a local variable is used and performance is better than when a parameter is used, then the problem is that sampled statistics are superior to actual statistics for the execution. In that case, the OPTIMIZE FOR UNKOWN hint might be a better choice. It could also just mean that the statistics are out of date. Hard to know without seeing the code and both execution plans.

"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