Are you using stored procedures with recompile option?

Category: Performance
Item: SPs with RECOMPILE

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 objects 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 WITH RECOMPILE option, the store procedure gets a new execution plan every time it runs.

That means SQL Server will execute the same action multiple times, degrading performance (high CPU usage).

How can you list all stored procedures with recompile option?

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

  1. EXECUTE master.sys.sp_MSforeachdb
  2. 'USE [?];
  3. SELECT DISTINCT Database_Name = DB_Name(),
  4. SP_Name = SO.name,
  5. ISR.SPECIFIC_SCHEMA As Schema_Name
  6. FROM sys.sql_modules AS SM
  7. LEFT OUTER JOIN master.sys.databases AS sDB ON SM.object_id = DB_id()
  8. LEFT OUTER JOIN dbo.sysobjects AS SO ON SM.object_id = SO.id and type = ''P''
  9. LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR 
  10. on ISR.Routine_Name = SO.name AND ISR.SPECIFIC_CATALOG = DB_Name()
  11. WHERE SM.is_recompiled=1 OPTION (RECOMPILE);';
Figure 1 – Query output.

How to fix them?

1. When the store procedure does not run often, this can probably be ignored.
2. If SP often runs, investigate further, recompilation is not necessarily the best solution.

More information

Microsoft – Recompile a Stored Procedure.
Frequent query recompilations – a SQL query performance killer – introduction, Milena Petrovic – SQLShack.

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *