6

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?

12

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

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

You can use the option recompile as mentioned by Erik for sure:

Additionally, you can also temporarily disable the caching of that particular execution plan using DBCC FREEPROCCACHE(Plan_handle)

You can find the cached plan handle (for that query) from below :

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* SP_or_Part of query_goes_here %';

Or you may use dmv sys.dm_exec_query_stats and then CROSS APPLY sys.dm_exec_sql_text to get the sql text query . Filter to find one of you're queries which will display the plan_handle_id

Once you get the plan_handle, you can simply execute:

DBCC FREEPROCCACHE(Plan_handle_id_from_above_goes_here)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.