SQL Server – “optimize for ad hoc workloads” progression

Background

It has been clean-out week for me.  Just have too much deadwood of interesting SQL Server tips that I have printed out over the years.

In this post, will cover Joe Sack’s nice post on a Server Setting known as “Optimize for ad hoc workloads“:

Joe Sack – Measuring impact of “optimize for ad hoc workloads” via CACHESTORE_SQLCP

http://blogs.msdn.com/b/joesack/archive/2011/03/28/measuring-impact-of-optimize-for-ad-hoc-workloads-via-cachestore-sqlcp.aspx

Preface

We will measure the impact of this setting in a couple of SQL Server versions; specifically MS SQL Server v2008R2 and v2012.

Lab

We will use the same table Joe Sack used.  But, change the query a bit.  Joe prints out the result of the query.  On the other hand, we will capture the query’s output in a variable.

And, also use “set nocount on“.  This minor differences will simply save memory on our client.

Create and Populate Table

Quick Explanation of what we will do:

  1. Create Schema ( optimizeforadhocworkloads ), if it does not exist
  2. Create Table [optimizeforadhocworkloads].[bloat], if it does not exist
  3. Using Insert/Go, insert N records; in our case a thousand records

set nocount on
go

use [DBLab]
go

--optimize for ad hoc workloads
if schema_id('optimizeforadhocworkloads') is null
begin
  exec('create schema [optimizeforadhocworkloads] authorization [dbo]')

end
go

if object_id('[optimizeforadhocworkloads].[bloat]') is null
begin

	CREATE TABLE [optimizeforadhocworkloads].[bloat]
	(
		col01 uniqueidentifier
	);

end

GO

truncate table [optimizeforadhocworkloads].[bloat]
go

--Add 1000 records
INSERT [optimizeforadhocworkloads].[bloat]
VALUES (NEWID())
GO 1000

Measuring Dipstick

We will consult with a couple of Dynamic Management Views.

  • sys.dm_os_memory_cache_counters
  • sys.dm_exec_cached_plans

And, here are the queries we will be running …

sys.dm_os_memory_cache_counters

v2005/v2008/v2008-R2


Select
           [when] = 'After'
	 , [name]
	 , [type]

	 --, SUM([pages_kb]) As [single_pages_kb]
	 , SUM([single_pages_kb])  As [single_pages_kb]

	 --, SUM([pages_in_use_kb]) As [multi_pages_kb]
	 , SUM([multi_pages_kb]) As [multi_pages_kb]

from sys.dm_os_memory_cache_counters

WHERE type = 'CACHESTORE_SQLCP'

Group By [name],[type]

v2012


Select
           [name]
	 , [type]

	 , SUM([pages_kb]) As [single_pages_kb]
	 --, SUM([single_pages_kb])  As [single_pages_kb]

	 , SUM([pages_in_use_kb]) As [multi_pages_kb]
	 --, SUM([multi_pages_kb]) As [multi_pages_kb]

from sys.dm_os_memory_cache_counters

WHERE type = 'CACHESTORE_SQLCP'

Group By [name],[type]

sys.dm_exec_cached_plans

Aggregate


select
	  objtype
	, count(*) as number_of_plans
	, avg(cast(size_in_bytes as bigint)) as avg_size_in_Bytes
        , sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs
	, avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
group by objtype
order by
            sum(cast(size_in_bytes as bigint)) desc

sys.dm_exec_cached_plans

Raw Contents


select
	  dmvDECP.cacheobjtype
	, dmvDECP.objtype
        , dmvDECP.size_in_bytes
	, dmvDEST.*

from   sys.dm_exec_cached_plans dmvDECP

	cross apply sys.dm_exec_sql_text(dmvDECP.plan_handle) dmvDEST

order by

         dmvDECP.size_in_bytes desc

Query to create SQL Cache Bloat

Here is Joe’s query for creating SQL Cache bloat.

A quick outline of what he does:

  1. He declares a cursor that covers the entire population of our bloat table
  2. He reads the id from the cursor
  3. And, accesses the table using the contents of the cursor

-- Now let's make some bloat
DECLARE @NEWID varchar(36)

DECLARE curBloat CURSOR FOR
SELECT col01
FROM   [optimizeforadhocworkloads].[bloat]
ORDER BY col01

OPEN curBloat

FETCH NEXT FROM curBloat
INTO @NEWID;

EXEC (' set nocount on; declare @col01 varchar(36); SELECT @col01 = col01 FROM [optimizeforadhocworkloads].[bloat] WHERE col01 = ' + '''' + @NEWID + '''')

WHILE @@FETCH_STATUS = 0
BEGIN

	FETCH NEXT FROM curBloat
	INTO @NEWID;

	EXEC (' set nocount on; declare @col01 varchar(36); SELECT @col01 = col01 FROM [optimizeforadhocworkloads].[bloat] WHERE col01 = ' + '''' + @NEWID + '''')

END

CLOSE curBloat;
DEALLOCATE curBloat;

go

Result – Raw Data

v2008-R2 – Optimize for adhoc workload – Off

SQLServer-v2008-R2-OptimizeOff


v2008-R2 - Optimize for adhoc workload - On

SQLServer-v2008-R2-OptimizeOn


v2012 - Optimize for adhoc workload - Off

SQLServer-v2012-OptimizeOff

v2012 - Optimize for adhoc workload - On

SQLServer-v2012-OptimizeOn

Result – Tabulated

Product Version CACHESTORE_SQLCP – BF CACHESTORE_SQLCP – BF Adhoc (Avg) Adhoc (Total)
 v2008R2-Off  200  41264  41 KB  39 MB
 v2008R2-On  256  1264  556 B  0 MB
 v2012-Off  1912  2600  49 KB  0 MB
 v2012-ON  1912  2160 352 B  0 MB

Analysis

There are a couple of deductions that we apparently can make.

  • In MS SQL Server 2008/R2
    • The “Optimize for adhoc workload” makes quite a bit of difference
      • The difference is apparent in terms of the size of individual adhoc query plan and the summarized footprint for all adhoc query plans
  • In MS SQL Server 2012
    • The setting results in smaller query plan size for individualized queries
    • But not as much in total plan size, as the number of resultant plans is dramatically fewer

Summary

Please do your own analysis.  It is breathtaking how much Engineering MSFT is putting into this product; even between releases, which are in fact shorter and tighter than they used to be.

Leave a Reply

Please log in using one of these methods to post your comment:

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