Ad hoc запросы руководства

Пересказ статьи Kathi Kellenberger. What is an ad hoc query?

Ad hoc запрос — это отдельный запрос, не включенный в хранимую процедуру и не параметризованный или подготовленный. В зависимости от установок сервера, SQL Server может параметризовать некоторые операторы, изначально написанные как ad hoc запросы. Ad hoc не означает динамический.

Вот простой пример 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. Инструменты мониторинга также могут помочь вам идентифицировать непроизводительные запросы, которые нуждаются в настройке.

In today’s article, we will learn how to improve SQL Server Ad Hoc Queries Performance and what Ad Hoc Query means.

Ad Hoc Query

The word ad hoc comes from Latin and means “for purpose”.

In other words, it refers to a query written in case the written query is written for a purpose and has no continuation or rarely.

If I need to explain with an example, imagine that you have a table with user information and let’s assume that a dynamic query is made over this table over a different user number.

In this case, the written query is actually an Ad Hoc query.

When you run a query in SQL Server, the execution plan is created for the query run by the SQL Server Query Optimizer and the query runs through this plan.

The execution of the said execution plan can be completed in a few milliseconds for a simple query,

while it may take longer for complex queries.

For this reason, SQL Server keeps the execution plan information it creates for each running query in its cache in case the same query is run again.

Think about it, even if you run it only once, SQL Server creates an execution plan for your query to run and keeps it in its cache so that you can run it again in the future.

Of course, if there is no suitable execution plan for your query to run,

a new query plan is created by SQL Server Query Optimizer.

In order to increase the cache efficiency of the execution plans of the infrequently used queries that entered our lives with SQL Server 2008,

it introduced “Optimize for Ad hoc Workloads”, a feature at the presentation level.

When the query is compiled for the first time, instead of keeping the execution plan,

the Compiler Plan stores the Stub value. If the query is run again, it converts the Stub value to the execution plan.

There are two methods you can use to determine whether this feature we have described is on or off.

The first one is SQL Server Management Studio (SSMS) Right click on the server in Object Explorer and then click Properties,

then in the advanced menu the “Optimize for ad hoc workloads” section defaults to False,

which means that when a query is compiled and executed, your entire plan will be placed in cache .

You can use TSQL to learn whether this feature is active or passive for GUI -On screen- learning.

The following query will solve your need.

The query result is;

Before making our example work, I do the execution plan cleaning with the following command as the first step.

Of course, restarting the SQL Server service will ensure that the cache – cache – is cleared, but we do not prefer this.

I will be making our example with AdventureWorks2019, one of Microsoft’s sample databases, on SQL Server 2019 Developer Edition.

The aforementioned “Optimize for Ad Hoc Workloads” feature is currently disabled.

I ran the following query.

After running my query, I run the following query to get the query plan.

Although I ran the query for the first time, “106496” byte was used for this query and execution plan on the cache.

In the query I run on AdventureWorks, I make changes to the BusinessEntityID,

run it again and check the execution plans again.

Query1:

Query2:

Query3:

After running the 3 queries above, we review the execution plan details again.

As you can see, apart from the BusinessEntityID = 3 query we ran before,

a separate execution plan was created for the other three queries, and each plan occupied the same size of space on the cache.

Let’s activate the “optimize for ad hoc workloads” feature and run our queries again.

For this process, it will be sufficient to run the following query.

Query output;

Let’s clear the plan caches again and run the queries we have run before, in order.

Let’s run the queries we ran above one by one and check the plan list again.

With the activation of the feature, we see the Complied Plan Stub value as you can see.

A decrease is observed in the size of the cache.

Let’s run the same query with the same parameter more than once and examine the plan details again.

It is no longer a Compiled Plan Stub state, so we know that the query being executed runs multiple times – 12 times in our example – and its size will not change in the next run.

What if we change the BusinessEntityID in our example and run the query again? I run the query again as BusinessEntityID = 20.

A different plan has been created and stands as the Compiled Plan Stub.

The next run of the same query will change the Compiled Plan Stub value to Compiled Plan.

We have observed how effective the “Optimize for Ad Hoc Workloads” feature is in improving the performance of Ad Hoc queries.

After examining the Ad Hoc queries on the system you are managing,

evaluating how often and what size they are, you can work on activating the feature.

Are you always trying to perform complex operations in a short amount of time? In the world of data analysis, speed is essential. In fact, the need to quickly find insights and take action is one of the most important trends in analytics in recent years. 

To accomplish this task efficiently and effectively, many organizations have looked to empower their teams to ask questions on the fly, without needing prebuilt dashboards, with ad hoc queries. Ad hoc queries are used to find information quickly from data; often this includes massive volumes of data, housed in a cloud data platform, and exposed through some sort of business intelligence solution. 

When opting for an ad hoc query instead of writing long SQL analytics functions or crafting configurations, users can quickly interrogate their data, without the time intensive work that goes into creating more traditional queries. The most powerful of these solutions pair a self service analytics experience with this ad hoc query. This makes it easier for anyone, not just those with technical knowledge or coding skills, to efficiently extract actionable insights quickly — saving both time and energy. Read further in this post as we dive into what an ad hoc query is and why it’s beneficial for your business.

What is an ad hoc query?

Ad hoc query refers to user-defined searches that are used to gain insight into a given data set without requiring any predefined dashboards, drill paths, and coding. By leveraging these types of queries, users can access specific information when and where they need it. Ad hoc queries are particularly important for business analytics and decision-making as it provides an efficient, bespoke way for users to examine trends, patterns, and any other variables related to the data set. This type of query allows users to ask specific questions, instead of just asking the questions defined by the analytics team when a dashboard or report was initially created. Furthermore, ad-hoc queries can be used to compare datasets side by side to gain a better understanding of the data’s characteristics and relationships.

Ad-hoc queries are often used in conjunction with data visualization tools, allowing users to see the results of their queries quickly and easily. The value of these tools extend the ad hoc query experience to the visualization through interactive data visualizations, allowing users to continually interrogate and dig deeper into their data.

Top benefits of ad hoc queries

Ad hoc queries give users the ability to ask questions and receive answers in real time. This creates opportunities for businesses to gain valuable insights and make decisions quickly, giving them an edge over their competitors. The most valuable combination of this ad hoc analysis capability is with a self-service experience. In doing so, not only can analytically skilled users quickly ask questions, any user can ask questions of their data, extending the benefit of ad hoc queries beyond data teams throughout an organization. 

Here are 5 benefits of ad hoc queries:

Increased data exploration

Ad hoc queries provide a flexible environment that allows users to ask questions in a variety of different ways. This gives users the ability to explore data on their own, quickly adapt their queries to meet the changing needs of their business, and take action on those changing needs.

Improved accuracy

Ad hoc queries can help businesses get more precise answers by allowing them to continue to ask follow up questions to identify potential issues of data quality, focus on specific data parameters, access the most updated data, and obtain more detailed information than traditional query systems.

Faster execution

Ad hoc queries are typically faster than traditional query systems, especially modern analytics tools built purposefully to handle data at cloud volumes. This can help businesses get their answers quicker, allowing for more efficient decision-making processes.

Better ROI from data investments

Ad hoc queries make it easier for users to ask questions and receive answers in real time; as mentioned, the best make it possible for every kind of user to do so. This helps drive a better user experience, which leads to better adoption, and ultimately, enhanced ROI when compared to traditional query systems.

See how companies are generating 289% ROI and $6.3M in value with ad hoc querying from ThoughtSpot

Cost savings

Ad hoc queries can help businesses save money, as they require less technical resources to answer questions. When paired with a self service business intelligence layer, business users can ask their own questions and get answers from the data themselves, meaning technical teams can break free from endlessly tweaking reports and focus on more strategic data initiatives.

5 examples of ad hoc queries

Ad hoc queries are an important tool for any data analyst or business intelligence professional and can help data teams hit data and analytics KPIs, while helping business users get more value from analytics. They enable dynamic, on-the-fly analysis and can be used to quickly answer specific questions or explore unknown relationships between data sets. Here are five examples of how ad hoc queries can help with data exploration and insight:

1. Identifying Trends

Ad hoc queries can be used to identify trends within a data set or between different sets of data. This could help answer questions such as what is the average age of people in a certain zip code, or how have sales of a particular product changed over time?

2. Comparing Data Sets

Ad hoc queries can also be used to quickly compare data sets to find correlations or differences between them. For example, if you wanted to compare sales of two different products at different stores, an ad hoc query could be used to quickly find the results.

3. Generating Reports

Ad hoc queries can also be used to generate reports, such as a customer list or a summary report on sales over a certain period. This is especially useful when dealing with large amounts of data that would otherwise take too long to process manually.

4. Spotting Anomalies

Ad hoc queries can be used to quickly identify any anomalies or inconsistencies in the data that may not be immediately obvious, such as unusually high or low values for certain variables.

5. Updating Reports

Ad hoc queries can also be used to update reports or dashboards on the fly, keeping them up-to-date with the latest data. This could help in cases where there are frequent changes to the data set being analyzed, such as sales figures for a particular product over time. This would enable the analyst to quickly update their reports without having to manually re-run the query each time.

What to look for in an ad hoc query tool

When searching for the best ad hoc query tool, there are five key features to look for. 

User-friendly interface. 

The interface should be easy to use and understand, allowing users to quickly access and manipulate data without needing extensive technical knowledge or training. For most companies, this means using an ad hoc query tool with a self service front end built around familiar consumer concepts like search and AI-generated suggestions. This removes all the query creation from the user, who instead asks questions in simple, natural language. This makes it simpler to quickly view data insights, generate interactive reports and charts, and take action

Direct data connectivity

With ad hoc queries, you’re often looking for updated information to a specific question. It’s critical your ad hoc query tool isn’t analyzing data that’s out of date if you want to build trust with users while driving the appropriate outcomes. In evaluating an ad hoc query tool, make sure you can connect natively with your data so your query results are always as fresh and current as the underlying data.

Strong scalability. 

Ad hoc queries are only as valuable as the data they run against. Often, the value is in the details. A top-notch ad hoc query tool should be able to handle massive data volumes without suffering in terms of experience or performance. It should also be able to easily incorporate new data sources and provide users with the ability to customize their own queries and reports.

Comprehensive reporting capabilities. 

The best ad hoc query tools offer a range of reporting options, interactive visualizations, customizable charts and graphs like bar charts and histograms, that can help visualize data in a more meaningful way. Look for features like report templates and automated scheduling so users can quickly generate standard or complex reports without having to manually enter data.

Reliable security measures. 

Many ad hoc query tools include encryption and authentication features that can help keep sensitive data secure. Look for tools that offer robust user control settings, secure access protocols, and other measures to protect against unauthorized access or tampering.

Get the insights you need in minutes

An ad hoc query is a great way to get specific insights from your data without having to rely on someone else to generate a report. If your organization would benefit from empowering anyone being able to safely and reliably ask and answer their own data questions with ad hoc queries, ThoughtSpot can help. Sign up for a free trial today and see for yourself.

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:

SQL Server will parameterize this simple query:

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:

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:

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.

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.

Что такое Adhoc запрос?

Adhoc запрос — запросы узкой специализации. Их создают для решения проблемы, которая возникает один раз. В целом сам термин «Adhoc» применяют, чтобы обозначить инструмент для выполнения конкретной задачи, без общей тенденции. Чаще всего Adhoc запрос — это выражение на языке запросов SQL. Его создает подготовленный пользователь вручную или с помощью графического инструмента доступа к данным.

До момента выдачи Adhoc запрос определить невозможно. Он состоит из динамически сконструированного SQL, который чаще всего создают с помощью резидентных инструментов запросов.

К примеру, если станет известно, что у конкретного пользователя в таблице Users содержится неточная информация о стране, то Adhoc запросом будет такой код:

Adhoc запросы и отчеты зачастую используют в системах бизнес-аналитики (BI) для получения ответа на узкоспециализированные и специфические вопросы, которые в силу своих особенностей отсутствуют в основном отчете. Также существует Adhoc-тестирование — полная импровизация для выявления ошибок, при котором не применяют известные правила или схемы.

См. также
Асинхронный код отслеживания
MYSQL
Фрейм
Атрибуция ссылок
API (application programming interface)