While I was answering a question, I had to revisit what happens when using different flavors of recompile hints with stored procedure when they call inner stored procedures. I like when this happens, because there are so many little details I forget.
Anyway, the TL;DR is that if you have nested stored procedures, recompiling only recompiles the outer one. The inner procs — really, I should say modules, because it includes other objects that compile query plans — but hey. Now you know what I should have said.
If you want to play around with the tests, you’ll need to grab sp_BlitzCache. I’m too lazy to write plan cache queries from scratch.
CREATE OR ALTER PROCEDURE dbo.inner_sp AS BEGIN SELECT COUNT_BIG(*) AS records FROM sys.master_files AS mf; END; GO CREATE OR ALTER PROCEDURE dbo.outer_sp --WITH RECOMPILE /*toggle this to see different behavior*/ AS BEGIN SELECT COUNT_BIG(*) AS records FROM sys.databases AS d; EXEC dbo.inner_sp; END; GO
--It's helpful to run this before each test to clear out clutter DBCC FREEPROCCACHE; --Look at this with and without --WITH RECOMPILE in the proc definition EXEC dbo.outer_sp; --Take out the proc-level recompile and run this EXEC dbo.outer_sp WITH RECOMPILE; --Take out the proc-level recompile and run this EXEC sp_recompile 'dbo.outer_sp'; EXEC dbo.outer_sp; --You should run these between each test to verify behavior --If you just run them here at the end, you'll be disappointed EXEC sp_BlitzCache @DatabaseName = 'Crap', @QueryFilter = 'procedure', @SkipAnalysis = 1, @HideSummary = 1; EXEC sp_BlitzCache @DatabaseName = 'Crap', @QueryFilter = 'statement', @SkipAnalysis = 1, @HideSummary = 1;
After each of these where a recompile is applied, you should see the inner proc/statement in the BlitzCache results, but not the outer proc.
It’s important to understand behavior like this, because recompile hints are most often used to help investigate parameter sniffing issues. If it’s taking place in nested stored procedure calls, you may find yourself with a bunch of extra work to do or needing to re-focus your use of recompile hints.
Of course, this is why I much prefer option recompile hints on problem statements. You get much more reliable behavior.
And, as Paul writes:
For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using
OPTION (RECOMPILE)has another significant advantage over
WITH RECOMPILE: Only
OPTION (RECOMPILE)enables the Parameter Embedding Optimization.
Thanks for reading!