{"id":90801,"date":"2021-05-04T16:55:28","date_gmt":"2021-05-04T16:55:28","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90801"},"modified":"2021-05-04T19:37:23","modified_gmt":"2021-05-04T19:37:23","slug":"what-is-an-ad-hoc-query","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/opinion\/editorials\/what-is-an-ad-hoc-query\/","title":{"rendered":"What is an ad hoc query?"},"content":{"rendered":"

Someone recently asked me which queries are ad hoc in SQL Server. An ad hoc query is a single query not included in a stored procedure and not parameterized or prepared. Depending on the server settings, SQL Server can parameterize some statements initially written as ad hoc queries. Ad hoc doesn\u2019t mean dynamic<\/em>.<\/p>\n

Here\u2019s a simple ad hoc query example in SQL Server:<\/p>\n

SELECT LastName, FirstName\r\nFROM Person.Person;<\/pre>\n
SQL Server will parameterize this simple query:<\/p>\n
SELECT LastName, FirstName\r\nFROM Person.Person\r\nWHERE BusinessEntityID = 7;<\/pre>\n
One answer I found when searching the question \u201cwhat is an ad hoc query\u201d said that ad hoc queries are built by combining answers from a web form. That\u2019s actually one way to create a dynamic query, which may or may not be ad hoc. If a dynamic query is parameterized, it\u2019s not an ad hoc query.<\/p>\n
Here\u2019s an example of a dynamic query that is parameterized (prepared), so it\u2019s not ad hoc:<\/p>\n
DECLARE @SQL NVARCHAR(MAX);\r\nDECLARE @ID INT;\r\nDECLARE @Param NVARCHAR(MAX);\r\n\r\nSET @SQL =\r\nN'SELECT LastName, FirstName\r\nFROM Person.Person\r\nWHERE BusinessEntityID = @ID';\r\n\r\nSET @ID = 1;\r\nSET @Param = N'@ID INT ';\r\n\r\nEXEC sp_executesql @SQL, @Param, @ID = @ID;<\/pre>\n
However, if there are no parameters, the query will remain ad hoc. Here is an example of an ad hoc query that also happens to be dynamic:<\/p>\n
DECLARE @SQL NVARCHAR(MAX);\r\nSET @SQL =\r\nN'SELECT LastName, FirstName\r\nFROM Person.Person;'\r\n\r\nEXEC sp_executesql @SQL;<\/pre>\n
Why are ad hoc queries useful?<\/h2>\n
In many cases, a developer or DBA may run an ad hoc query once and then never run it again. On the other hand, the same query can run thousands of times a day from an application, yet it\u2019s still may be an ad hoc query. Depending on the query, it may not make sense to include it in a stored procedure or parameterize it.<\/p>\n
Ad hoc queries are neither bad nor good; like anything else, it all depends on how they are used. Here\u2019s an interesting article<\/a> from Phil Factor about troubleshooting some poorly performing ad hoc statements.<\/p>\n
What is an ad hoc query in a database?<\/h2>\n
To find out if SQL Server treats the query as ad hoc, you can examine the object type in the plan cache. This query is from \u201cMicrosoft SQL Server 2012 Internals\u201d by Kalen Delaney et al. Note that you may need to add more filters on [text] if it returns so many rows that you can\u2019t find your query.<\/p>\n
SELECT usecounts, cacheobjtype, objtype, [text]\r\nFROM sys.dm_exec_cached_plans P\r\nCROSS APPLY sys.dm_exec_sql_text (plan_handle)\r\nWHERE cacheobjtype = 'Compiled Plan'\r\n\u00a0 \u00a0 \u00a0 AND [text] NOT LIKE '% dm_exec_cached_plans%';<\/pre>\n
You\u2019ll see the object type Adhoc<\/em> for an ad hoc query. For parameterized queries, you\u2019ll also see a row with the object type of Prepared<\/em>. Stored procedure calls will return Proc<\/em>, and there are a few others.<\/p>\n
What is the Optimize for Ad Hoc Workload setting?<\/h2>\n
As you may imagine, a large number of queries could each run only once on a given system. To avoid having these take up space in the plan cache, enable the Optimize for Ad Hoc Workload setting. Then, the first time a query runs, only a stub of the plan is stored in the cache. If it runs again, then SQL Server will store the entire plan.<\/p>\n
Conclusion<\/h2>\n
It\u2019s easier to say what an ad hoc query isn\u2019t than to say what it is. Ad hoc queries are not necessarily bad things, just part of a typical workload for SQL Server. If you suspect that some ad hoc queries are causing problems, you can begin investigating by using Kalen\u2019s query. A monitoring tool can also help you identify poorly performing queries that need tuning.<\/p>\n
\n
Commentary Competition<\/h4>\n
Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.<\/p>\n<\/div>\n\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Someone recently asked me which queries are ad hoc in SQL Server. An ad hoc query is a single query not included in a stored procedure and not parameterized or prepared. Depending on the server settings, SQL Server can parameterize some statements initially written as ad hoc queries. Ad hoc doesn\u2019t mean dynamic. Here\u2019s a……<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[47125,53,143531],"tags":[143540,143543,143542,143541,5842],"coauthors":[11292],"class_list":["post-90801","post","type-post","status-publish","format-standard","hentry","category-editorials","category-featured","category-t-sql-programming-sql-server","tag-ad-hoc-query","tag-ad-hoc-query-example","tag-ad-hoc-sql-queries","tag-adhoc-query","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90801","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=90801"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90801\/revisions"}],"predecessor-version":[{"id":90807,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90801\/revisions\/90807"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90801"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90801"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}