According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure.
This is probably the most direct way to force a recompile, if you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time. There are some significant disadvantages to watch out for though.
CREATE PROCEDURE dbo.FooForProfit @Blood INT, @Sweat BIGINT, @Tears SMALLINT
-- Do that foo-do that you do so well (for profit)!
Alternatively you can just add the hint after the execute thusly:
EXECUTE dbo.FooForFun @Joy = 42 WITH RECOMPILE;
- This will hurt performance, and most likely give you tainted performance values.
WITH RECOMPILE after the
EXECUTE statement might not be possible depending on how you're executing the stored procedures.
- The compiled plan will not be cached, nor will the performance information be maintained1.
The compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats.
- This will disable the Parameter Embedding Optimization1.
Parameter Embedding Optimization takes this process a step further: query parameters are replaced with literal constant values during query parsing. The parser is capable of surprisingly complex simplifications, and subsequent query optimization may refine things even further.
I feel it is important to note that some very smart and experienced people like Aaron Bertrand make it a point not to go down this route. I initially thought this would be your best bet I'm now convinced that this probably isn't the ideal solution.
If you can't or don't want to modify the stored procedure and your data access mechanism doesn't let you append the query hint you can manually force a recompile of the stored procedure using another provided stored procedure. One nice up side to this route is you can force a recompile at will so you can easily compare executions with or without the compilation penalty.
EXEC sp_recompile N'dbo.FooForFame';
- This is going to add a separate step to your process to reset your test bed.
You could probably add this to a trigger linked to executing your stored procedure. <-- Don't do that because you don't want to encourage the maniacs or create more.
RECOMPILE Query Hint
Like it says in the docs, this will give you more fine grained control over what gets recompiled if a stored procedure has multiple queries.
RECOMPILE is a useful alternative to creating a stored procedure that uses the
WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled.
RECOMPILE is also useful when you create plan guides.
It will also let you recompile statements that aren't within a stored procedure. That is useful for obvious reasons. Here is a sample:
- This will be slightly more work if you really do have multiple statements in a stored procedure.