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 (https://blog.sqlauthority.com)
33 Comments. Leave new
Hi Pinal,
Really very good digging. thanks for sharing this useful tip with us.
Ritesh Shah
great article. thank you!
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
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
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.
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
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.
how does this differ from Plan Caching introduced in SQL Sever 2005?
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
Work in MS SQL Server Express 2008, but there is no affect, I mean, it still show the ad hoc query in cache.
Can you explain to me about this?
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”.
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.
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
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.
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)
I am getting out put as Compiled Plan Stub
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.
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.
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.
Greate Tips
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?
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.