Can I disable execution plan caching for debugging purposes?

I am trying to isolate poor performing queries in our application.

While query execution plan caching is great in production it is interfering with our debugging efforts.

Is there are way to temporary disable execution plan caching in SQL Server (any further) so I can get a reliable & true indicator of the parse & compile time on SQL each time I run the query batch?

According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure.

WITH RECOMPILE

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
  WITH RECOMPILE
AS
  -- 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;

The problems:

• This will hurt performance, and most likely give you tainted performance values.

• Adding 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.

SP_RECOMPILE

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';

The problems:

• 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.

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:

SELECT
  Peace 
FROM dbo.TheWorld
OPTION (RECOMPILE);

The problems:

• This will be slightly more work if you really do have multiple statements in a stored procedure.

StackExchange Database Administrators Q#122023, answer score: 14