Does OPTION (RECOMPILE) Prevent Query Store from Saving an Execution Plan?

Recompile hints have been tough to love in SQL Server for a long time. Sometimes it’s very tempting to use these hints to tell the optimizer to generate a fresh execution plan for a query, but there can be downsides.

• This can drive up CPU usage for frequently run queries
• This limits the information SQL Server keeps in its execution plan cache and related statistics in sys.dm_exec_query_stats and sys.dm_exec_procedure_stats
• We’ve had some alarming bugs where recompile hints can cause incorrect results. (Oops! and Whoops!)
• Some queries take a long time to compile (sometimes up to many seconds), and figuring out that this is happening can be extremely tricky when RECOMPILE hints are in place

The new SQL Server 2016 feature, Query Store may help alleviate at least some of these issues. One of my first questions about Query Store was whether recompile hints would have the same limitations as in the execution plan cache, and how easy it might be to see compile duration and information.

Let’s Turn on Query Store

I’m running SQL Server 2016 CTP3. To enable query store, I click on the database properties, and there’s a QueryStore tab to enable the feature. I choose “Read Write” as my new operation mode so that it starts collecting query info and writing it to disk:

If you script out the TSQL for that, it looks like this:

USE [master]
GO
ALTER DATABASE [ContosoRetailDW] SET QUERY_STORE = ON
GO
ALTER DATABASE [ContosoRetailDW] 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), 
DATA_FLUSH_INTERVAL_SECONDS = 900, 
INTERVAL_LENGTH_MINUTES = 60, 
MAX_STORAGE_SIZE_MB = 100, 
QUERY_CAPTURE_MODE = ALL, 
SIZE_BASED_CLEANUP_MODE = AUTO)
GO

And Now Let’s Test Drive that RECOMPILE Hint

Now that Query Store’s on, I make up a few queries with RECOMPILE hints in them and run them– some once, some multiple times. After a little bit of this, I check out and see what query store has recorded about them:

SELECT 
  qsq.query_id,
  qsq.query_hash,
  qsq.count_compiles,
  qrs.count_executions,
  qsq.avg_compile_duration,
  qsq.last_compile_duration,
  qsq.avg_compile_memory_kb,
  qsq.last_compile_duration,
  qrs.avg_logical_io_reads,
  qrs.last_logical_io_reads,
  qsqt.query_sql_text,
  CAST(qsp.query_plan AS XML) AS mah_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt on qsq.query_text_id=qsqt.query_text_id
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
WHERE qsqt.query_sql_text like '%recompile%';
GO

Note: I’ve kept it simple here and am looking at all rows in sys.query_store_runtime_stats. That means that if I’ve had query store on for a while and have multiple intervals, I may get multiple rows for the same query. You can add qrs.runtime_stats_interval_id to the query to see that.

Here’s a sample of the results:

YAY! For all my queries that were run with RECOMPILE hints, I can see information about how many times they were run, execution stats, their query text and plan, and even information about compilation.

And yes, I have the execution plans, too – the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works.

Want to Learn More about Query Store and Recompile?

In this post, I just talked about observing recompile overhead with Query Store. Grant Fritchey has an excellent post that addresses the question: what if you tell Query Store to freeze a plan for a query with a recompile hint? Will you still pay the price of recompile? Read the answer on Grant’s blog here.