Quiz: Recompile behavior and pros and cons in SQL Server

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

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

1.  Marmite
2.  CPU
3.  Storage
4.  Memory

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

1.  The RECOMPILE query hint
2.  The undocumented DECOMPILE hint
3.  The RECOMPILE hint in the header of the procedure

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

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

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

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

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?

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

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

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

Scroll down for the answer key :point_down:

Answer Key

• A1: 2. CPU
• A2: 3. 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).
• A3: 3. 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.
• A4: 2. 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.
• A5: 2. 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!
• A6: 2. 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 1. 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.)