It is an understood thing that Microsoft SQL Server (as many dbms) makes optimizations in the background for the user to try make queries faster. Well, it’s a nice and good thing and of course if we spend less time for optimization we got more time for beer. We could be happy at this point, but let see what happens when using stored procedures.
Sql server generates a so-called ‘execution plans’ and executes queries by them. It stores these plans and decides by them which indexes will be used at the next running.
The problem starts at stored procedures that contains optional parameters. In that time we don’t need to give all of the parameters. In that case we should need different query plans with different given parameters.
When sql server executes a stored procedure it makes a single query plan for each stored procedures based on the given parameters at the first run. The server uses this plan for all runs and generates new query plan only in this cases:
Let’s see a simple example for query plan caching (using northwind sample database):
Let’s see the actual execution plans:
As we can see sql server uses the same query plan for both executing: it uses the index for company name when user searches by ID.
The best solution that I found and has well expressed had written by Erland Sommarskog (Sql server MVP). He says we can solve it this ways:
Static sql. Use WITH RECOMPILE option when executing stored procedure. At this time we make sql server to recompile the stored procedure and it must generate a brand new query plan. In that case the problem is given when we call the procedure many times. Recompile needs processor time and many recompiles causes performance problems.
2.: Dynamic sql. Create a query string by hand dynamically and run it with sp_executesl:
The complete source code is: