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
- La version est lue dans la chaîne
@@version. L'ordre des arguments deCHARINDEXet le décalage ont été corrigés par rapport à la source (la chaîne recherchée et la chaîne source étaient inversées, et le décalage était d'un caractère de trop) ; le script n'a pas été exécuté sur un serveur pour cet article. - Pour SQL Server 2012 et versions ultérieures, la taille du buffer pool provient de
committed_kb; les versions antérieures utilisentbpool_committed. Countest le nombre de plans à usage unique ;PctBufferPoolest leur mémoire en part du buffer pool engagé.