Symptoms
When you frequently back up or restore a database in Microsoft SQL Server 2008, the memory usage increases gradually. Additionally, the size of the procedure cache grows at a steady rate.
You can see this problem by running the following statements.
SELECT p.plan_handle,CONVERT (varchar, GETDATE(), 126) AS runtime, LEFT (p.cacheobjtype + ‘ (‘ + p.objtype + ‘)’, 35) AS cacheobjtype,p.usecounts, p.size_in_bytes / 1024 AS size_in_kb, stat.total_worker_time/1000 AS tot_cpu_ms, stat.total_elapsed_time/1000 AS tot_duration_ms, stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads, LEFT (CASE WHEN pa.value=32767 THEN ‘ResourceDb’ ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))END, 40) AS dbname,sql.objectid, CONVERT (nvarchar(50), CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE (REPLACE (sql.[text],CHAR(13), ‘ ‘), CHAR(10), ”)END) AS procname, REPLACE (REPLACE (SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1, CASE WHEN stat.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text])) ELSE stat.statement_end_offset/2 – stat.statement_start_offset/2 + 1END), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS stmt_textFROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sqlWHERE pa.attribute = ‘dbid’ ORDER BY p.plan_handle DESCIn the result, you may find that there are many records in which the values of the stmt_text column resemble the following:
select @media_set_id = media_set_id from msdb.dbo.backupmediaset where media_uuid = N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}
insert msdb.dbo.backupmediaset (media_uuid, media_family_count, software_name, software_vendor_id, MTF_major_version, mirror_count, is_password_protected, is_compressed) values (N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}’, 1, @param0, 4608, 1, 1, 0, 1)
select @media_set_id = @@identity end select @media_count = media_count from msdb.dbo.backupmediafamily where media_set_id = @media_set_id and family_sequence_number = 1These operations are the read operations and the write operations to the msdb backup history tables.
Resolution
The procedure cache grows because the backup statements or the restore statements are not fully parameterized. Each statement results in a prepared statement in the cache that is never reused.
