SQL Server Query Store – What is an Ad-Hoc Query?

query-storesql server

I've been doing a deep dive into the SQL Server Query Store and I often see references to 'ad-hoc' queries. However, I haven't seen what the Query Store determines an ad-hoc query to be. I've seen places where it could be inferred to be a query without parameters or a query executed just one time. Does a formal definition exist for this? I don't mean in general. I mean as it relates to the Query Store.

As an example, this page shows an example of removing ad-hoc queries from the query store, but it appears the criteria it's using is an execution count of just one. This seems to be an odd definition of an ad-hoc query. BTW, if you go to the page, search for 'Delete ad-hoc queries'.

Best Answer

After a little bit of searching, I wasn't able to find a concrete and satisfying source from Microsoft's documentation to answer this question. There are many good third-party descriptions and definitions of adhoc/ad-hoc/ad hoc out there, but for the specificity of this question I think one close to the source is ideal.

Moving past generic (but still accurate) definitions like this SO post (thanks SqlWorldWide), if we look at what the documentation does say on the topic, it agrees with what you've mentioned about the definition being based off of the number of executions, I think to the point that we can take it as fact.

The article on optimizing for ad hoc workflows says,

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database Engine compiles the batch ... and adds the full compiled plan to the plan cache.

So it would appear the the ad hoc query server configuration option also uses the definition of a single execution as the definition of ad hoc. If the query continues to get executed and generates the same plan it would no longer be treated as such.

The article on Best Practices for Query Store also aligns with this,

Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. If the ratio is close to 1 your ad-hoc workload generates different queries.

This, of course, is for queries that are not already used as stored procedures, parameterized, etc. because they can be recognized and treated appropriately right off the bat.

So based on all this we can say that a query is treated as ad hoc if:

  • It is not parameterized
  • It is not programmatically stored in the database (stored proc, function, trigger, etc.)
  • The same query is only executed one time OR The same query is executed multiple times but generates a different query plan for each subsequent execution.
Related Question