| Comments

SSRS Report with Datasets from a Stored Procedure with Parameters, Runs Very Slowly

In most cases, SQL Server is smart enough to figure out the best execution plan for a Stored Procedure with Parameters. There are times, however, that this execution plan does not work. This was the case in an SSRS report I created which was pulling from a Stored Procedure with "Date" parameters. If you have a slow running report, who’s Dataset is pulling from a Stored Procedure with Parameters, you might want to research Parameter Sniffing.

Parameter Sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure. It uses the calling parameters that are passed, the first time a stored procedure is executed. Parameter Sniffing happens when SQL Server is forced to Compile or Recompile a Stored Procedure that is not already in the procedural cache. Now each time the Stored Procedure is run, it uses the newly created optimal execution plan.

My report was taking 8 minutes to run on 12,000 records. If I ran the query in SQL Server Management Studio, it ran in less than 1 second. What I did to get my report executing properly was to add last line to my Stored Procedure.

You wouldn't want to do this for all of your Stored Procedures with parameters, but in certain cases, it could be bonus.

comments powered by Disqus