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

The cache store “CACHESTORE_SQLCP” represents cached ad-hoc query plans, server-side cursors and prepared statements. One way to gather memory allocation values for this specific cache store is by using the following query:

SELECT single_pages_kb, multi_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

You can also see allocations to CACHESTORE_SQLCP via DBCC MEMORYSTATUS (example output below for one node):

CACHESTORE_SQLCP (node 0),KB

VM Reserved,0

VM Committed,0

Locked Pages Allocated,0

SM Reserved,0

SM Committed,0

SinglePage Allocator,12728

MultiPage Allocator,1968

There has already been much written about cache bloat due to ad hoc query plans and I won’t rehash it here (Kimberly Tripp has a few great posts on this topic – including one post that lists several posts from different authors on the subject). There are multiple ways to reduce this bloat (for example - move to using parameterized queries, force parameterization). One of the easier methods includes enabling the “optimize for ad hoc workloads” option.

The purpose of this post is to connect the dots between CACHESTORE_SQLCP and enabling the “optimize for ad hoc workloads" option (note that you can also use sys.dm_exec_cached_plans to see the impact on size_in_bytes by plan and reference the cacheobjtype of Compiled Plan versus Compiled Plan Stub). The following demonstration simply shows the impact of executing several ad hoc queries and measuring the significant allocation differences in CACHESTORE_SQLCP. In my testing – single_pages_kb was equal to 13,072 without plan stubs and then 760 after enabling the “optimize for…” option. This below demo is intended to be stepped through statement by statement:

-- Tested on 10.50.1765

-- This demo assumes optimize for ad hoc workloads is off

-- (And yes please only run this demo on a test environment)

USE [master]

GO

EXEC sp_configure 'show advanced options',1

RECONFIGURE

GO

EXEC sp_configure 'optimize for ad hoc workloads',0

RECONFIGURE

GO

EXEC sp_configure 'show advanced options',0

RECONFIGURE

GO

CREATE DATABASE [QueryBloat];

GO

USE [QueryBloat];

GO

CREATE TABLE dbo.Bloat (col01 uniqueidentifier);

GO

INSERT dbo.Bloat

VALUES (NEWID())

GO 500

-- Clear out adhoc queries, prior to baseline

DBCC FREESYSTEMCACHE('SQL Plans')

-- Take baseline

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- In my case, I saw single_pages_kb = 120

-- Now let's make some bloat

DECLARE @NEWID varchar(36)

DECLARE curBloat CURSOR FOR

SELECT col01

FROM dbo.Bloat

ORDER BY col01

OPEN curBloat

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

END

CLOSE curBloat;

DEALLOCATE curBloat;

-- Checking again, I see single_pages_kb = 13,072

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- Now let's enable "optimize for ad hoc workloads"

EXEC sp_configure 'show advanced options',1

RECONFIGURE

GO

EXEC sp_configure 'optimize for ad hoc workloads',1

RECONFIGURE

GO

EXEC sp_configure 'show advanced options',0

RECONFIGURE

GO

-- Clear out adhoc queries for our second test

DBCC FREESYSTEMCACHE('SQL Plans')

-- Take baseline - I see single_pages_kb = 120

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- Make some bloat again

DECLARE @NEWID varchar(36)

DECLARE curBloat CURSOR FOR

SELECT col01

FROM dbo.Bloat

ORDER BY col01

OPEN curBloat

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

END

CLOSE curBloat;

DEALLOCATE curBloat;

-- Measuring impact - single_pages_kb = 760 (versus 13,072)

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'