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'