SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.

SQL Server 2008 has feature of optimizing ad hoc workloads. Before we move to it, let us understand the behavior of SQL Server without optimizing ad hoc workload.

Please run following script for testing. Make sure to not to run whole batch together. Just run each command separately to really see the effect of subject of article.

Download complete script of this article here.

/* Test 0 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks
GO
/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift
GO
/* Check if Adhoc query is cached.
It will return one result */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

Now let us check result of this script. It is clear from result that when we ran query once it cached its plan in memory. If we never run this again in future or if we have just ran as part of building longer query the cache plan of this query is just waste of memory.

SQL SERVER - 2008 - Optimize for Ad hoc Workloads - Advance Performance Optimization cachetest0

Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008.

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

We will now run the code for Test 1 which is almost same as Test 0. Make sure to clean the cache and buffer before running the query to create real life scenario of live case.

/* Test 1 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks
GO
/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift
GO
/* Check if Adhoc query is cached.
It will not return any result */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE 'SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

We can clear see now as we have advance option enabled we do not have query cache planed stored in database.

SQL SERVER - 2008 - Optimize for Ad hoc Workloads - Advance Performance Optimization cachetest1

We are interested to know now that if we run the batch more than 1 time it will cache its execution plan.  With advance option of optimizing ad hoc workload.

/* Test 2 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
/* Run Adhoc Query two  Time */
SELECT * FROM HumanResources.Shift
GO 5
/* Check if Adhoc query is cached.
It will return result with Adhoc Query ran two times*/
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM
sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;
GO

From our image it is quite clear that when the batch is ran for more than 1 time it caches its execution plan. This is generic behavior with or without turning on advance option.

SQL SERVER - 2008 - Optimize for Ad hoc Workloads - Advance Performance Optimization cachetest2

This may be very simple to see from the top but if you are using SQL Server 2008 and have millions of ad hoc query running every day you wil realize how important this feature is. This feature improves performance by relieving memory pressure by not storing the single time used compiled plans.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – AWE (Address Windowing Extensions) Explained in Simple Words
Next Post
SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users

Related Posts

33 Comments. Leave new

  • Hi Penal,
    There isn’t any impact of settings as defined the blog.
    It still leaves execution plan in every scenario.

    No record is wrong because because of a very minor mistake
    TEXT LIKE ‘SELECT * FROM HumanResources.Shift%’

    TEXT LIKE ‘%SELECT * FROM HumanResources.Shift%’

    I need to know one thing is it possible to reuse any Execution Plan intentionally for any query again?

    Reply
  • Your own write-up offers confirmed beneficial to myself.

    It’s quite helpful and you really are naturally very well-informed in this field.

    You get popped our sight in order to numerous views on
    this particular matter using intriquing, notable and solid content.

    Reply
  • My doubt is somewhat related to sp optimization, i have 30 Sp’s on my DB, only one SP causes parameter sniffing issue, after i fixed it with the using local variables, then it started working fine, few of the blogs suggesting to have “OPTION (OPTIMIZE FOR UNKNOWN)” instead of local variables, is it good to implement above syntax in all the SP’s or only the SP’s comes under parameter sniffing issue and why performance issue in one of the Sp instead of all, Please clarify

    Reply
  • Hi Pinal,
    whenever i am going to execute below query it showing error like this

    “DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

    These two commands are which invoking this error can u please suggest me for this error
    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS

    Reply
  • Niraj Sevalkar
    June 9, 2014 12:12 pm

    Hi Pinal,
    I am getting the error while executing following commands in above demos

    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS

    Error is like :
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Reply
  • Niraj Sevalkar
    June 9, 2014 12:27 pm

    Hi Pinal,
    Its working now by mistake i haven’t observed this Error Message

    Reply
  • You are my inspiration but in time article you explained wrong point. Its the Compiled Plan and Compiled Plan Stub that matters. So far here what I come to know

    Reply
  • HI Pinal,
    I am always find procedure cache hit ratio in my server below 90%. Even i am having “optimize for ad hoc workloads” is true in my box. Can you please help me what should i do?

    Reply
    • Do you have any performance issue?

      Reply
      • Yes. I have the performance issue and sql server always show the memory consumption 100%. and one more thing what should i do to decrease CPU uses.

  • Jitesh KHilosia
    July 21, 2017 11:44 am

    Hi Pinal,
    After we have found many Adhoc quries are getting executed in my system, we have enabled the option ‘Optimize the Adhoc Worklod’. After that we have observed that some of the SQl Server:Memory Manager counters like Lock Blocks, Lock Blocks Allocated indicates very high values which seems unusual. Also same time SQL Cache Memory is getting dropped to 2 MB.

    Can you please explain?

    Reply

Leave a Reply

Menu