Plan cache blowing up
JCSystems
Posts: 1 New member
in SQL Backup
Hi, during a recent audit of one of our SQL Server instances we noticed that the plan cache was full of thousands of plans for the same query which I believe is specific to Red Gate's SQL Backup. This was the query that was being cached so aggressively:
SELECT TOP 1 a.type,
a.backup_set_uuid,
a.first_lsn,
a.last_lsn,
a.checkpoint_lsn,
a.database_backup_lsn,
a.media_set_id, c.name,
a.has_backup_checksums,
a.is_damaged
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name
COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
WHERE b.physical_device_name = **obfuscated**
ORDER BY a.media_set_id DESC
If this is indeed a Red Gate query, any chance this query could be parameterized? It would prevent it from blowing up the plan cache and creating false positives.
If this is indeed a Red Gate query, any chance this query could be parameterized? It would prevent it from blowing up the plan cache and creating false positives.
