How to Recompile Stored Procedure? – Interview Question of the Week #260

Question: How do you recompile a stored procedure? EXEC sys.sp_recompile N’dbo.NameofSP’ marks that procedure for a new compilation the next time it runs. Replace the placeholder with the actual schema-qualified name.

EXEC sys.sp_recompile N'dbo.NameofSP';

That answers the syntax question. The more useful question is why you want a different plan. The usual reason is parameter sniffing: a plan compiled for one parameter can be a poor fit for another.

Parameter sniffing itself is normal and often beneficial. The problem is an unsuitable reused plan for the workload, not simply that a parameter was examined. Recompilation is one possible intervention, not the only remedy and not something every procedure needs.

There are different scopes:

• sp_recompile: invalidate the named object’s plan and compile when it next runs. It doesn’t execute the procedure for you.
• EXEC … WITH RECOMPILE: request a freshly compiled plan for that invocation.
• OPTION (RECOMPILE): recompile the individual statement carrying the hint, rather than every statement in the procedure.

Recompilation costs CPU and prevents that execution from benefiting from ordinary plan reuse. A regularly recurring issue deserves investigation of estimates, data skew and version-appropriate plan features, such as local variables, OPTIMIZE FOR UNKNOWN or the database-scoped parameter sniffing setting. SQL Server also recompiles automatically after relevant changes; parameter sensitivity is not the only reason recompilation exists.

Recompiling is not a cure for slow procedures, it is one tool for a bad reused plan, and it costs CPU every time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.