SQL-Ex blog

Вот простой пример ad hoc запроса в SQL Server:

SELECT LastName, FirstName

FROM Person.Person;

SQL Server параметризует этот простой запрос:

SELECT LastName, FirstName

FROM Person.Person

WHERE BusinessEntityID = 7;

При поиске по запросу "что такое ad hoc запрос" я обнаружила один ответ, говорящий, что ad hoc запросы - это запросы, построенные при комбинации ответов на веб-форме. На самом деле это один из способов создать динамический запрос, который может являться ad hoc запросом, а может таким и не быть. Если динамический запрос параметризован, он не является ad hoc запросом.

Вот пример динамического запроса, который параметризован (подготовлен), поэтому он не является 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;

Однако если параметров нет, запрос останется ad hoc. Вот пример ad hoc запроса, который так же является динамическим:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL =

N'SELECT LastName, FirstName

FROM Person.Person;' 

EXEC sp_executesql @SQL;

Чем полезны ad hoc запросы?

Во многих случаях разработчик или DBA может один раз выполнить ad hoc запрос, и больше никогда не использовать его. С другой стороны, один и тот же запрос может выполняться тысячи раз за день из приложения, и при этом, возможно, оставаться ad hoc запросом. В зависимости от запроса может не иметь смысла включать его в хранимую процедуру или параметризовать его.

Ad hoc запросы не являются ни плохими, ни хорошими; как и все остальное, это зависит от того, как они используются. Сошлюсь на интересную статью от Phil Factor, посвященную устранению проблем с некоторыми неэффективными ad hoc операторами.

Что такое ad hoc запрос в базе данных?

Чтобы выяснить, рассматривает ли SQL Server запрос как ad hoc, вы можете проверить тип объекта в кэше плана. Вот запрос из книги "Внутри Microsoft SQL Server 2012" Kalen Delaney и др. Замечу, что вам может потребоваться добавить больше фильтров на [text], если он вернет слишком много строк, чтобы отыскать ваш запрос.

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%';

Вы увидите тип объекта Adhoc для ad hoc запроса. Для параметризованных запросов вы также увидите строку с типом объекта Prepared. Вызовы хранимых процедур будут возвращать Proc, и имеется несколько других.

Что такое параметр Optimize for Ad Hoc Workload?

Представим себе систему, на которой каждый из большого числа запросов может выполняться только раз. Чтобы избежать ситуации, когда они занимают место в кэше планов, включите параметр Optimize for Ad Hoc Workload. Тогда при первом выполнении запроса в кэше сохраняется только заглушка плана. Если запрос выполняется снова, SQL Server сохранит весь план.

Заключение

Легче сказать, чем не является ad hoc запрос, чем он является. Ad hoc запросы не обязательно являются плохими вещами, они просто часть типичной рабочей нагрузки SQL Server. Если вы подозреваете, что некоторые ad hoc запросы вызывают проблемы, вы можете начать исследование при помощи запроса Kalen. Инструменты мониторинга также могут помочь вам идентифицировать непроизводительные запросы, которые нуждаются в настройке.