Xrhrft

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'.

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.

For ad-hoc queries, the object_id column in the sys.query_store_query DMV will be 0, as stated in the sys.query_store_query documentation:

object_id:

ID of the database object that the query is part of (stored procedure,
trigger, CLR UDF/UDAgg, etc.). 0 if the query is not executed as part
of a database object (ad-hoc query).

You can identify ad-hoc queries based on that value, even though it is not explicitly stated as "this is the definition of ad-hoc queries". :)

The term ad-hoc is used to refer to queries that have only been executed once. This is consistent with the definition used for the database setting "Optimize for ad-hoc workloads".

The link you reference regards deleting ad-hoc queries includes this specific definition "Delete ad-hoc queries: This deletes the queries that were only executed only once and that are more than 24 hours old."

Ad-hoc from sys.dm_exec_cached_plans dmv's objtype column in docs.microsoft.com.

Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls.

One use of this column is to see in case of Queries with multiple plans to see if the multiple plans are caused by Adhoc.

    SELECT  DISTINCT 

        QCP.objtype

        ,MultipleQ.PlanCount

        ,qStat.query_hash

        ,sText.text AS QueryText

FROM ( 

        SELECT query_hash,

               COUNT(query_hash) AS PlanCount

        FROM sys.dm_exec_query_stats

        GROUP BY query_hash

    ) AS MultipleQ

INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash

INNER JOIN sys.dm_exec_cached_plans  QCP

    ON QCP.plan_handle = qStat.plan_handle

CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText

CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp

WHERE PlanCount > 1

ORDER BY MultipleQ.PlanCount DESC