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

Question

I’m trying to understand the plan cache content of a sql server.

so my questions are:
1. What is the difference between ad hoc and prepared plans?
2.What should I know about it when trying to optimize the sql server plan cache?

Answer 1

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