What is an ad hoc query?

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’t mean dynamic.

Here’s a simple ad hoc query example in SQL Server:

SELECT LastName, FirstName
FROM Person.Person;

SQL Server will parameterize this simple query:

SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID = 7;

One answer I found when searching the question “what is an ad hoc query” said that ad hoc queries are built by combining answers from a web form. That’s actually one way to create a dynamic query, which may or may not be ad hoc. If a dynamic query is parameterized, it’s not an ad hoc query.

Here’s an example of a dynamic query that is parameterized (prepared), so it’s not ad hoc:

DECLARE @SQL NVARCHAR(MAX);
DECLARE @ID INT;
DECLARE @Param NVARCHAR(MAX);

SET @SQL =
N'SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID = @ID';

SET @ID = 1;
SET @Param = N'@ID INT ';

EXEC sp_executesql @SQL, @Param, @ID = @ID;

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:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL =
N'SELECT LastName, FirstName
FROM Person.Person;'

EXEC sp_executesql @SQL;

Why are ad hoc queries useful?

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’s 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.

Ad hoc queries are neither bad nor good; like anything else, it all depends on how they are used. Here’s an interesting article from Phil Factor about troubleshooting some poorly performing ad hoc statements.

What is an ad hoc query in a database?

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 “Microsoft SQL Server 2012 Internals” 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’t find your query.

SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
      AND [text] NOT LIKE '% dm_exec_cached_plans%';

You’ll see the object type Adhoc for an ad hoc query. For parameterized queries, you’ll also see a row with the object type of Prepared. Stored procedure calls will return Proc, and there are a few others.

What is the Optimize for Ad Hoc Workload setting?

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.

Conclusion

It’s easier to say what an ad hoc query isn’t 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’s query. A monitoring tool can also help you identify poorly performing queries that need tuning.

 

 

The post What is an ad hoc query? appeared first on Simple Talk.

from Simple Talk https://ift.tt/3b3ag5X
via