March 20, 2012 at 12:59 pm
I have a stored procedure that takes about 20 seconds to run when I run it in SQL Server Management Studio. There are 2 temporary tables in this stored procedure. When I run the report that calls this stored procedure the report takes about 15 minutes to return.
When I looked at the SQL profiler when the report is run I see an exec sp_reset_connection every 4-6 seconds during this 15 minute wait. When the stored procedure/report finally runs the profiler shows a duration of 987854 for the stored procedure while the profiler duration for the stored procedure run directly through SSMS is 4600.
Any thoughts?
March 22, 2012 at 9:35 am
Try creating local variables for all the input parameters in the SP, set the local variables equal to the input parameters and then use the local variables in the body of the SP. I have seen a lot of parameter sniffing issues with input parameters on stored procudures and this will generally resolve the problem.
March 22, 2012 at 9:53 am
As Daniel said, this sounds like parameter sniffing. Fixing for that should help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 12:57 pm
That did it!!!!!!! Thanks so much!
March 22, 2012 at 1:53 pm
Good.
With reports that have parameters, I tend to code for parameter sniffing as a precaution. I have run into this problem too often with reports.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2012 at 2:24 pm
I agree. Whenever I am making a stored procedure to be used in a report I always create local variables and set their values to the input parameters and then use the local variables in the body of the report.
March 23, 2012 at 2:11 am
Thanks guys, solved the same issue for me.
Ian Cockcroft
MCITP BI Specialist
March 23, 2012 at 9:00 am
Ian C0ckcroft (3/23/2012)
Thanks guys, solved the same issue for me.
Sweet - good to hear.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply