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.