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:
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);
How to fix them?
- When the stored procedure does not run often, this can probably be ignored.
- If the stored procedure runs frequently and further investigation is needed, recompilation may not be the best solution.
