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.

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.

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.

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 (http://blog.sqlauthority.com)

About these ads

30 thoughts on “SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

  1. Hi Pinal,

    Is it so that the following code runs only in SQL Server 2008, because when I am running this on SQL Express 2005 it giving error “Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
    The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option.”

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

    I checked in sys.configurations table under master db and there is no option such as ‘optimize for ad hoc workloads’ available over there.

    Thanks
    Pradeep Nair

  2. Pradeep,

    It is possible to run in SQL Server 2005 and it works fine. You need to turn on advance option first as I have suggested in the blog post.

    sp_CONFIGURE ’show advanced options’,1
    RECONFIGURE
    GO

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

    Regards,
    Pinal

  3. Hi Pinal,

    Thanks for the response. I am executing both the command together and separately. But I am still getting the same error. The first command “sp_CONFIGURE ‘show advanced options’, 1″ its running successfully and giving a response as “Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.”. But the second script is throwing the same error which I mentioned in my previous comment. Thanks.

    Regards
    Pradeep Nair

  4. Works in 2008

    I just tested it in SQL 2005 Standard 64 bit SP2

    There is no “optimize for ad hoc workloads” option, if you just run SP_CONFIGURE to display all options

    sp_CONFIGURE ‘show advanced options’,1
    RECONFIGURE
    GO

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

    gives me
    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
    The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option.

  5. Thanks Jerry Hung and Pinal…I thought so, because when I ran a select statement on sys.configurations table under master database, the option was not there.

    And, Pinal I read your latest blog entry which you wrote today(26 Mar 08 – SQL Server – Fix : Error : Msg 15123….). Thanks once again.

    Regards,

    Pradeep Nair

  6. Hi ,

    Nice article.

    I have a stored procedure which is running fine in sql server 2000. But when i upgrade it to sql server 2008 it running for long time and never stopped execution. I tried to stop and rerun but no use.

    Can you please let me know what action to be taken on this to get it run again. FYI the execution time for this SP in 2000 is about 60 mins.

    • Hello Henry,

      In SQL Server 20055 and earlier version the cached plan of ad hoc queries are cleared from memory only on memory pressure. Then SQl engine identify the cached plans that are least useful. This approach affect the performance of running job. In SQL Server 2008, the option “optimize for ad hoc workloads” implements a proactive approach to avoid memory pressure and improve memory utilization.

      Regards,
      Pinal Dave

  7. Hi Pinal,

    Thanks for your post.
    I have some comments.

    The script (complete file) contains a bug. The statement “SP_CONFIGURE ‘optimize for ad hoc workloads’,0″ should be “SP_CONFIGURE ‘optimize for ad hoc workloads’,1″ otherwise the feature won’t be enabled.

    The result of the second query is different for me, in fact it does return a row where cacheobjtype = “Compiled Plan Stub”.

    The result of the third query is different for me, in fact it does return a row where usecounts is 4 and not 5 as you state. This would mean that when the query is run for the second time the “Compiled Plan Stub” is replaced for a “Compiled Plan”.

  8. Hello Pinal,

    It was interesting your material is really helpful

    but the thing is in here

    how to delete the cached plans used less than 2 times

    i.e., I dont want to delete all cached plans only some of them.

  9. Hi Pinal,

    I am using SQL Server 2008 Stnd on Windows 2008 Stnd.

    the server had 4 GB memory and SS was using 3.5 GB
    now we increased to 10 GB and SS is using 8.5 GB.

    We want to know, out of 17 databases on the default instance, which one is using how much memory or which application is using more memory?
    also
    memory usage break-down by databases?

    please reply ASAP as it is in production.

    Thanks for your help and time.

    Shoaib

  10. I ran this on 6 Dec 2010 using SQL Server 2008 Enterprise Edition SP1. With ‘optimize for ad hoc workloads’ = 1, running a query once produces one record when I query sys.dm_exec_cached_plans, but column cacheobjtype shows ‘Compiled Plan Stub’. When I run the same query again, cacheobjtype shows ‘Compiled Plan’.

    This seem to be a change from when you ran your tests.

    Good article. I will use this information to demonstrate to some co-workers.

  11. I have tried same example and I am not getting expected result.

    my version of Sql server is following.

    Can you please help in this?

    Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

  12. I am getting the following error in SQL Server 2008 R2.

    “pctfreemem is [ 2 ] threshold is 5 [ MEM - pctfreemem ]”

    I have enabled the ‘optimize for ad hoc workloads’,1

    though i am getting the memory issue. Installed RAM is 16GB and SQL Server Max server memory setting is 10240.

    Please guide me to resolve this problem. Server is in production state.

  13. I am getting the following error in SQL Server 2008 R2.

    “pctfreemem is [ 2 ] threshold is 5 [ MEM - pctfreemem ]”

    I have enabled the ‘optimize for ad hoc workloads’,1

    though i am getting the memory issue. Installed RAM is 16GB and SQL Server Max server memory setting is 10240.

    Please guide me to resolve this problem. Server is in production state.

    This is bit urgent. Please guide me.

  14. Lakshmi,

    Its a bit late when I am stumbling on this blog. I hope that issue is resolved by now. Your memory settings look good. HOwever, the error is not very descriptive. Looks like a kernal memory type pressure.Each solution has to evaluated indivifually as one solution does not fit all similiar issues.

  15. 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?

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

  17. 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.

  18. 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

  19. 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

  20. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s