4

We have a third party application which generates a lot of ad-hoc statements (our server has 16GB of memory and there are 140000 ad-hoc queries present in the cache).I've enabled 'Optimize for Ad-hoc Workloads' so the query cache doesn't get bloated. I've asked the supplier to adjust his queries but he doesn't want to change them.

My question is, what impact can a lot of ad-hoc queries have on my server. My first thought is a lot of compiles.

0

It can lead to multiple execution plans being stored for each query. Slight changes in the way query text is structured (like position of carriage returns etc) will be interpreted differently by the engine and assumed to be a different query.

This link has a script to find multiple plans: https://www.brentozar.com/blitzcache/multiple-plans/

Ideally queries would be parameterized such that this problem is less pronounced.

This can affect the amount of space available for cached plans (due to plan cache bloat) as well as create a large variance in runtimes for queries as sub-optimal plans may be selected. Recompilation when the plan isn't in cache can be the other factor

1

It can impact the amount of memory available. Each query plan takes a little memory away from SQL Server.

Also, each time a plan has to be created, SQL Server has to compile it. This means it uses CPU which could be used for other processes. If you have a high CPU contention than that could become a problem.

This query can help you see how large the query plans actually are:

SELECT TOP 1000
    st.text ,
    cp.cacheobjtype ,
    cp.objtype ,
    cp.refcounts ,
    cp.usecounts ,
    cp.size_in_bytes ,
    cp.bucketid ,
    cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE   cp.cacheobjtype = 'Compiled Plan'
    AND cp.objtype = 'Prepared'
ORDER BY cp.usecounts DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.