sp_Blitz Result: Stored Procedure WITH RECOMPILE Option

When you create a stored procedure using the WITH RECOMPILE option, it gets a brand new execution plan every time it runs.  This can be good for high performance queries because they get a plan perfect for the variables that are passed in.  However, this causes increased CPU time due to compiling the execution plan over and over.

This part of our SQL Server sp_Blitz script checks the INFORMATION_SCHEMA.ROUTINES management view looking at the ROUTINE_DEFINITION field.  It looks for “%WITH RECOMPILE%” in the stored proc.  Granted, this is a really bad way to find these stored procedures, and it’ll give you false positives if somebody happens to use the phrase WITH RECOMPILE in their stored proc, like in a comment.  Hey, nobody’s perfect.

To Fix the Problem

Check out Kendra Little’s video on The Use and Abuse of RECOMPILE in SQL Server.

Return to sp_Blitz or Ask Us Questions