Q1. What is the primary resource used when SQL Server compiles an execution plan?

•  Marmite
•  CPU
•  Storage
•  Memory

[Show Answer] CPU

Q2. Which RECOMPILE hint didn’t register at all on the re-compilations/sec performance counter in our demo?

•  The RECOMPILE query hint
•  The undocumented DECOMPILE hint
•  The RECOMPILE hint in the header of the procedure

[Show Answer] The RECOMPILE hint in the header of the procedure. Think about it this way: putting RECOMPILE in the stored procedure header prevents caching altogether. This makes it just count as a compile, not a re-compile (because there’s nothing to remove).

Q3. When we used RECOMPILE as a query hint, what impact did this have on sys.dm_exec_query_stats?

•  We didn’t see any information at all for that query
•  We saw information about all 500 executions
•  We only saw the information about one execution, the most recent
• It cleared the entire plan cache

[Show Answer] We only saw the information about one execution, the most recent. Using RECOMPILE as a query hint results in the last-run version of the execution plan and related query-level statistics remaining in cache.

Q4. When we used RECOMPILE as a query hint in a procedure, what impact did this have on sys.dm_exec_procedure_stats?

•  We didn’t see any information at all for the procedure
•  We saw information about all 500 executions
•  We only saw the information about one execution, the most recent
• It cleared the entire plan cache

[Show Answer] We saw information about all 500 executions. With RECOMPILE as a query hint, we still saw runtime statistics for 500 executions of the stored procedure itself in sys.dm_exec_procedure_stats. That’s because we are recompiling an individual statement, not the whole procedure.

Q5. When we used RECOMPILE in the header of a procedure, what impact did this have on sys.dm_exec_query_stats and sys.dm_exec_procedure_stats?

•  We saw information about all 500 executions
•  We didn’t see any information at all for the procedure
•  We only saw the information about one execution, the most recent
• It cleared the entire plan cache

[Show Answer] We didn’t see any information at all for the procedure. When we put RECOMPILE in the header of the procedure, we were saying not to cache the execution plan of the query at all. This has the side effect of making it invisible in these DMVs. Bummer!

Q6. What is one limitation of testing a procedure by running EXEC WITH RECOMPILE?

•  If the procedure you’re running has a recompile hint in it, it does a double recompile backflip
•  If you’re using nested code, this only applies to the outermost procedure
•  It requires sysadmin permissions
•  It won’t work if the procedure includes temp tables or table variables

[Show Answer] If you’re using nested code, this only applies to the outermost procedure. As far as I know this isn’t officially documented, but in my testing I have found the RECOMPILE to only be applied to the outermost procedure – not any nested procedures. (That makes sense, too: if you wanted those to be executed with recompile, you should have to specify that on their own EXEC statement.)

Back to: Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes) > Recompile hints aren't created equal