SQL Server Brain Basher of the Week #005

Can I use sp_recompile with tables?

What would be your answer? Yes or no?

Answer should be Yes but it does not mean that we can recompile tables using this procedure.

Generally we use sp_recompile for invalidating existing plans in the cache for stored procedures, triggers or user-defined functions and plans will be regenerated with next run. This requires one parameter: Name of the procedure, user-defined function or trigger. Remember, this does not recompile procedures, triggers, and user-defined functions, it just marks existing plans as invalid. New plan will be created when we use the object again.

This allows us to pass a table name or view name as the parameter value. If the parameter value is either a table name or view name, it invalidates all existing plans of procedures, user-defined functions and triggers that have reference to the table or view. Similarly, new plans will be generated at the next run of referenced objects.

Let's test this. Execute the first statement few times and then execute the next statement. First statement executes a stored procedure in AdventureWorks2014 databases and second statement checks for Compiled Plans in the Cache.

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
   
USE AdventureWorks2014;
GO

-- First staement
-- Execute this three times
EXEC dbo.GetFrieghts 2011

-- Second statement
-- We should see 3 counts on compiled plan
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE
 TEXT LIKE '%GetFrieghts%';

Now, let's call sp_recompile for the table Sales.SalesOrderHeader which has been referenced by dbo.Getfrieghts procedure.

EXEC sp_recompile N'Sales.SalesOrderHeader';

Once done, execute the stored procedure once and see whether engine uses the same plan or a new plan.

-- Execute again - separately
EXEC dbo.GetFrieghts 2011

-- Check compiled plans
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE
 TEXT LIKE '%GetFrieghts%';

Surprise? Has the plan been invalidated and new plan has been created? It looks like no, old plan has been used again. Though we see the result looks like that, the reality is different. It has recompiled the stored procedure but not fully, only statements that have reference to the table. Let's test it.

Execute the first code set again. Once executed we have a compiled plan in the cache and it has been hit 3 times. Next, start the Profiler and add SP:CacheHit and SP:Recompile.

Run the Profiler. Execute the second code set that calls sp_recompile with the table and execute the stored procedure again.. Once executed, check the Profiler. This is what you should see.

As you see, two statements that have a reference to the table have been recompiled. Since the entire stored procedure has not been recompiled, the count of old compiled plan is getting increased, but remember, few parts of the procedure has been recompiled.