10

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

8

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

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". :)

0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.