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.
We’re still really early days on the Query Store feature and there’s a lot that we don’t know, but wow am I excited to learn more.