SQL Server Performance Tuning

Stored Procedures and RECOMPILE Option: When and Why to Use It

Updated October 29, 20252 min read

Stored procedure RECOMPILE: a blueprint beside a bin of identical crumpled ones

What is the RECOMPILE option?

The compilation is the process when a query execution plan of a stored procedure is optimized based on the current database object's state. This query execution plan is often stored in the cache to be quickly accessed.

Recompilation is the same process as a compilation, just executed again.

Why should you care about it?

When you use the stored procedure WITH RECOMPILE option, it gets a new execution plan every time it runs.

That means SQL Server will execute the same action multiple times, which can lead to performance degradation due to high CPU usage.

How can you list all stored procedures with a RECOMPILE option?

You can use the following script to list them from all databases:

T-SQL
EXECUTE master.sys.sp_MSforeachdb

USE [?];

SELECT DISTINCT Database_Name = DB_Name()
	,SP_Name = SO.name
	,ISR.SPECIFIC_SCHEMA AS Schema_Name
FROM sys.sql_modules AS SM
LEFT OUTER JOIN master.sys.databases AS sDB ON SM.object_id = DB_id()
LEFT OUTER JOIN dbo.sysobjects AS SO ON SM.object_id = SO.id
	AND type = '' P ''
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR ON ISR.Routine_Name = SO.name
	AND ISR.SPECIFIC_CATALOG = DB_Name()
WHERE SM.is_recompiled = 1
OPTION (RECOMPILE);
Figure 1 - Query output
Figure 1 - Query output

How to fix them?

  1. When the stored procedure does not run often, this can probably be ignored.
  2. If the stored procedure runs frequently and further investigation is needed, recompilation may not be the best solution.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜