Set procs to recompile to avoid plan cache noise

[BlitzErik]

Hi Ola,

We do this across the Blitz* scripts:

ALTER PROCEDURE [dbo].[sp_Blitz]
    @Help TINYINT = 0 ,
...
WITH RECOMPILE
AS

Which helps us to avoid interfering in anyone's plan cache, or one of our procs being detected by... well, another one of our procs. Heh.

This would cover all the non-dynamic SQL statements in your procs, but the dynamic bits would (I believe) need recompile hints individually.

On the plus side, the only one I've seen cause any kind of resource consumption is the call to sys.dm_db_index_physical_stats in dbo.IndexOptimize.

Low priority, but I appreciate your time.

Thanks,
Erik

[olahallengren]

Thank you for the suggestion.

For statements in my stored procedures that are executed many times (in while loops) the plan caching is important for performance.

I know that from looking at the performance of the stored procedure for customers with large number of databases on a server, or large number of objects in a database.

[BlitzErik]

Ah, okay, thanks. Makes sense.

Erik