Measure the plan cache used by single-use plans
Calculate the memory used by single-use ad hoc and prepared plans, and their share of the buffer pool.
The query below measures how much of the plan cache is taken by single-use ad hoc and prepared plans, and what share of the buffer pool that represents. Single-use plans are a common cause of plan cache bloat. The optimize for ad hoc workloads setting stores a small stub instead of the full plan on the first execution, which reduces this memory use.
SET NOCOUNT ON;
DECLARE @List TABLE
(
pk INT IDENTITY(1, 1),
handle VARBINARY(2000)
);
DECLARE @Handle VARBINARY(2000);
DECLARE @Min INT;
DECLARE @Bufferpool INT;
DECLARE @MB DECIMAL(19, 3),
@Count BIGINT,
@StrMB NVARCHAR(20),
@SqlVersion INT,
@sqlStmt NVARCHAR(400);
DECLARE @OptAdHoc VARCHAR(5);
SELECT @OptAdHoc = CASE value_in_use
WHEN 1 THEN 'TRUE'
ELSE 'FALSE'
END
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';
SELECT @SqlVersion = CAST(SUBSTRING(@@version, CHARINDEX('Microsoft SQL Server ', @@version) + 21, 4) AS INT);
IF @SqlVersion >= 2012
SET @sqlStmt = N'SELECT @iBufferpool=CAST(FLOOR((committed_kb)/1024.0) as int) FROM sys.dm_os_sys_info';
ELSE
SET @sqlStmt = N'SELECT @iBufferpool=CAST(FLOOR((bpool_committed*8)/1024.0) as int) FROM sys.dm_os_sys_info';
EXECUTE sp_executesql @sqlStmt,
N'@iBufferpool int OUTPUT',
@iBufferpool = @Bufferpool OUTPUT;
SELECT @MB = (SUM( CAST((CASE WHEN usecounts = 1 AND objtype IN ( 'Adhoc', 'Prepared' ) THEN size_in_bytes
ELSE 0
END ) AS DECIMAL(12, 2)) ) / 1024 / 1024 ),
@Count = SUM( CASE WHEN usecounts = 1 AND objtype IN ( 'Adhoc', 'Prepared' ) THEN 1
ELSE 0
END ),
@StrMB = CONVERT(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans;
SELECT @@SERVERNAME AS InstanceName,
@OptAdHoc AS Optimize_For_Adhoc,
@Bufferpool AS Bufferpool_MB,
@MB AS AdHoc_Queries_MB,
CAST(ROUND((@MB / @Bufferpool) * 100, 2) AS NUMERIC(5, 2)) AS PctBufferPool,
@Count AS [Count];
Notes
- The version is read from the
@@versionstring. TheCHARINDEXargument order and the offset were corrected from the source (the search string and the version string were swapped, and the offset was one character too far); the script was not run against a server for this article. - For SQL Server 2012 and later, the buffer pool size comes from
committed_kb; earlier versions usebpool_committed. Countis the number of single-use plans;PctBufferPoolis their memory as a share of the committed buffer pool.