Interview Question of the Week #063 – How to Recompile Stored Procedure for Specific Table?

Question: How do I recompile stored procedures that use a particular table? In the intended database, pass the table’s qualified name to sys.sp_recompile. It marks the referencing procedures, triggers and functions for recompilation when they next run.

This question came from a performance-tuning engagement rather than an interview. We had made substantial changes to a main table’s data and discussed whether the existing plans still suited the workload. It belongs in the series because understanding what the command actually does matters more than remembering its spelling.

-- Original commands, templates only in the intended sample database:
-- EXEC sys.sp_recompile N'Sales.Customer';
-- EXEC sys.sp_recompile N'dbo.uspGetBillOfMaterials';

-- Rehearse only in a fresh scratch database with unused names.
IF OBJECT_ID(N'dbo.InterviewRecompileDemo') IS NOT NULL
 OR OBJECT_ID(N'dbo.InterviewRecompileReadDemo') IS NOT NULL
 THROW 50001,'Use a fresh scratch database.',1;
CREATE TABLE dbo.InterviewRecompileDemo(ID int NOT NULL PRIMARY KEY);
INSERT dbo.InterviewRecompileDemo VALUES(1);
GO
CREATE PROCEDURE dbo.InterviewRecompileReadDemo @ID int
AS SELECT ID FROM dbo.InterviewRecompileDemo WHERE ID=@ID;
GO
EXEC dbo.InterviewRecompileReadDemo @ID=1;
EXEC sys.sp_recompile N'dbo.InterviewRecompileDemo';
EXEC dbo.InterviewRecompileReadDemo @ID=1;

Mark now, compile on the next execution

Passing Sales.Customer affects the referencing modules. Passing dbo.uspGetBillOfMaterials targets that module. The scratch example illustrates the table form without invalidating plans in an application or shared sample database.

Do not make this a daily maintenance habit. SQL Server already recompiles plans for appropriate events. Marking many modules can add compilation CPU and change the plans chosen on their next execution. A restart clears cached plans; it does not proactively compile every stored procedure.

If one problematic execution needs a fresh plan, a procedure call with WITH RECOMPILE may be narrower. If one statement needs it, OPTION (RECOMPILE) may be narrower still. Investigate statistics, parameter sensitivity and the actual plan before choosing the scope.

The recompilation guide compares the available scopes.

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