Sp_Recompile – does it work the way you expect?

Last week I was working on an assignment where I need to recompile all stored procedure where ever it is refereeing to a certain table. There are many options to recompile stored procedure. I opted to go with SP_Recompile where I will pass table name as parameter and this is meeting my requirement.

MSDN documentation says SP_Recompile “Causes stored procedures and triggers to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.”

SP:CacheInsert is logged when a sp plan get inserted to plan cache.

SP:Recompile is logged when a sp get recompiled.

Also when you pass table or view name as an argument to sp_recompile, MSDN documenation says “all the stored procedures or triggers that reference the table or view will be recompiled the next time that they are run.”

Now let’s see what it logs when you pass table name as parameter to sp_recompile.

Select event SP:CacheInsert ,SP:Recompile and Start profiler. Then run the below code.

CREATE    Procedure usp_getProductDetails
AS
Select ProductName, ProductLabel
From [dbo].[DimProduct] WHERE ProductName like '%MP3%'

Select ProductCategoryName, ProductCategoryLabel
From [dbo].[DimProductCategory] WHERE ProductCategoryName = '02'

GO
-- Call sp
EXEC usp_getProductDetails -- Running first time so it will log a SP:CacheInsert 

-- mark sp to recompile where ever the table DimProductCategory is referred 
Sp_recompile 'DimProductCategory'

GO
--Will get a message as “Object 'DimProductCategory' was successfully marked for recompilation.”

EXEC usp_getProductDetails
GO

When we call the sp again just after

Sp_recompile 'DimProductCategory'

It is recompiling the stored procedure but if you watch closely it is not recompiling the whole sp rather it only recompiles the statement where the table  is referred in the stored proecdure.