What Is Ad Hoc Query

What is Ad Hoc Query?

Ad hoc is latin for "for this purpose". You might call it an "on the fly" query, or a "just so" query. It's the kind of SQL query you just loosely type out where you need it

var newSqlQuery = "SELECT * FROM table WHERE id = " + myId;

...which is an entirely different query each time that line of code is executed, depending on the value of myId. The opposite of an ad hoc query is a predefined query such as a Stored Procedure, where you have created a single query for the entire generalized purpose of selecting from that table (say), and pass the ID as a variable.

What is the difference between ad hoc and prepared query in sql server plan cache?

What is the difference between ad hoc and prepared plans?

Adhoc query:

select * from t1

Prepared query:
Queries which substitute place holders in place of actual values are called Prepared statements.

Some Examples:

select * from t1 where id=@id

One more Example taken from wikipedia:

command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";

    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);

What should I know about it when trying to optimize the sql server plan cache?

There are whitepapers written about how to optimize plan cache.so i will try to keep it little..

Normally when a query is executed against SQL ,SQL compiles the plan and stores it in the plan cache .This plan cache is memory taken from buffer pool and different versions have different restrictions on how much amount of memory will be used

You know that memory is a precious resource and no amount of Hardware will be enough if you have leaks..

Assume you submit queries only once or twice and you tend to submit like this queries a lot.SQL will store the plan of this queries in plan cache which generally bloats PlanCache which is bad

There are different DMVS which will help you in digging through plan cache..

Query to find different type of objects are in plan cache :

select 
objtype,count(*) as countt,sum(size_in_bytes)*1024.0 as memoryinkb
 from   sys.dm_exec_cached_plans a
 group by objtype

Adhoc,prepared queries which are bloating plancache and are used only once:

select q.query_hash, 
    q.number_of_entries, 
    t.text as sample_query, 
    p.query_plan as sample_plan
from (select top 20 query_hash, 
            count(*) as number_of_entries, 
            min(sql_handle) as sample_sql_handle, 
            min(plan_handle) as sample_plan_handle
        from sys.dm_exec_query_stats
        group by query_hash
        having count(*) > 1
        order by count(*) desc) as q
    cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
    cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

To Delete statements which are bloating plan cache:

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)

SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 
        , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
        BEGIN
                DBCC FREESYSTEMCACHE('SQL Plans') 
                RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)
        END
ELSE
        BEGIN
                RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
                — Note: this is only a warning message and not an actual error.
        END
go

The above should give you an idea on where to start ,below are the must read topics and references :

1.http://www.sqlskills.com/blogs/kimberly/category/plan-cache/

2.http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx

3.https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

4.Must read article By SQLCAT on issues customer faced while using Prepare Statements

In referenced articles above,kimberely suggests to Enable Optimize for Adhoc workloads option,but i suggest test it first.here is an interesting thread on DBA.SE

Confusion about the term ad-hoc query

A "production" system has a lot of "canned queries". Such queries rarely change -- usually only when you "release" a new "version" of the "product".

An "Ad Hoc query" is a SELECT that you make up to look for something for which the canned queries don't help. It often comes from management asking something like "How many foobars happened last week?"

You will run that query once (or until you get the desired output), then toss it. Or you might develop it into a "canned query" for the "production" system, at which point it should no longer be called "ad hoc".

Are all Queries in MongoDB ad-hoc?

It is my understanding that, at the most basic of levels, an ad-hoc query allows for the developer to provide variables into the query. Meaning, the full query is only known at the time of execution.

Meaning, not all queries are ad-hoc, but MongoDB does support ad-hoc queries.

An example of an ad-hoc query in Mongo would be something like:

// this example uses node.js
const results = await db.collection.find({ name: req.query.name });

In the above example, req.query.name is only known at the time of execution, thus making our query an ad-hoc query.

Please let me know if you have any questions.

How to prevent recompile for ad hoc query

Any time an ad-hoc batch changes, even by a single character, it is recompiled.

To prevent a recompile, you need to pass the batch through using sp_executesql, and properly parameterize it. At this point, you will get parameter sniffing, unless you add the hint 'DISABLE_PARAMETER_SNIFFING'

set statistics time on;

EXEC sp_executesql
  N'
select * from ComplexTableValuedFunction(@o);
',
  N'@o bigint',
  @o = 3374707;

set statistics time off;

The actual EXEC statement does not have a query plan, so changing the value will not affect anything.

Note that a parameterized query from a client app such as C#/SqlClient actually uses sp_executesql anyway.

Where is the adhoc request option in Airflow 2.0.1?

Ad-Hoc query was removed in Airflow 2.0.0 due to security reasons.

Due to security concerns, the new webserver will no longer support the
features in the Data Profiling menu of old UI, including Ad Hoc Query,
Charts, and Known Events.

This is listed in change log.

---

Related Topics