LLI Wiki — My Digital Knowledge Base

Mesurer le cache de plans utilisé par les plans à usage unique

Calculer la mémoire occupée par les plans ad hoc et préparés à usage unique, et sa part du buffer pool.

La requête ci-dessous mesure la part du cache des plans occupée par les plans ad hoc et préparés à usage unique, et la part du buffer pool que cela représente. Les plans à usage unique sont une cause fréquente de saturation du cache des plans. Le paramètre optimize for ad hoc workloads stocke un petit stub à la place du plan complet lors de la première exécution, ce qui réduit cette consommation mémoire.

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];

Remarques

Mastodon LLIMastodon SocialFosstodon