![]() 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. In this post, I just talked about observing recompile overhead with Query Store. Want to Learn More about Query Store and Recompile? 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.Īnd yes, I have the execution plans, too – the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works. You can add qrs.runtime_stats_interval_id to the query to see that. 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. Note: I’ve kept it simple here and am looking at all rows in sys.query_store_runtime_stats. WHERE qsqt.query_sql_text like '%recompile%' JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id JOIN sys.query_store_plan qsp on qsq.query_id =qsp.query_id JOIN sys.query_store_query_text qsqt on qsq.query_text_id =qsqt.query_text_id If you script out the TSQL for that, it looks like this:ĬAST(qsp.query_plan AS XML) AS mah_query_plan I choose “Read Write” as my new operation mode so that it starts collecting query info and writing it to disk: To enable query store, I click on the database properties, and there’s a QueryStore tab to enable the feature. 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. After that, as a part of the query execution process, the query plan is generated and stored in. First it parses and normalizes the SQL Server statements then it compiles and optimizes the statements. The new SQL Server 2016 feature, Query Store may help alleviate at least some of these issues. At a high level, the query processor goes through multiple phases before producing a query plan in SQL Server. 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.We’ve had some alarming bugs where recompile hints can cause incorrect results.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.This can drive up CPU usage for frequently run queries.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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |