December 9, 2010 at 5:58 am
Commvault does all of our sql backups. Sql 2005 Enterprise x64 on Windows Server 2008R2 x64 with 64GB memory. Sql max memory set at 54GB. Plan cache operates at about 5GB normally, but empties at 10pm weeknights and haven't found the cause yet.
This KB article seems to refer to this condition exactly, but for sql 2008. Haven’t found anything on it for 2005. SP4 for 2005 is on it’s way but I’m not seeing where it addresses this. The backup queries are not parameterized.
http://support.microsoft.com/kb/961323
For us, the query below shows about 2000 plans in procedure cache related to backups and totaling about 4GB. What would you think of doing a cleanup of these, possibly on a regular basis?
-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (planhandle)
SELECT --p.plan_handle,
stat.creation_time,stat.last_execution_time,
--CONVERT (varchar, GETDATE(), 126) AS runtime,
LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35) AS
cacheobjtype,
p.usecounts, p.size_in_bytes / (1024*1024) AS size_in_mb,
--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 + 1
END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_text
FROM 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 sql
WHERE pa.attribute = 'dbid' and sql.[text] like '%msdb.dbo.backup%' and p.size_in_bytes / 1024>2000
ORDER BY stat.creation_time
December 9, 2010 at 3:05 pm
Are you using Lock Pages in Memory Privilege ?
If Parameterized queries are not an option, the try to implement Forced Parameterization. This will reduce the compilation overhead and the procedure cache size. But this should be well tested before implementing on the Prod Box.
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
Check these links before using Forced Parameterization
http://technet.microsoft.com/en-us/library/ms175037.aspx
http://www.sql-server-performance.com/articles/per/forced_parameterization_p1.aspx
Thank You,
Best Regards,
SQLBuddy.
December 9, 2010 at 3:47 pm
Yes we're using the lock pages privilege for our sql service account. The queries are apparently run by sql server, sql agent or the Commvault backup tool since they all involve the MSDB database. In other words , the sql is not anything we generate locally from our applications. I suppose we could test forced parameterization on a dev box first, for the MSDB database.
December 9, 2010 at 3:51 pm
Here are a couple examples of these queries in plan cache, occupying about 2mb each.
insert msdb.dbo.backupset ( backup_set_uuid, media_set_id, first_family_number, first_media_number, last_family_number, last_media_number, catalog_family_number, catalog_media_number, position, software_vendor_id, name, user_name, software_major_version, software_minor_version, software_build_version, time_zone, mtf_minor_version, first_lsn, last_lsn, database_backup_lsn, checkpoint_lsn, database_creation_date, backup_start_date, backup_finish_date, type, sort_order, code_page, compatibility_level, database_version, backup_size, database_name, server_name, machine_name, flags, unicode_locale, unicode_compare_style, collation_name, is_password_protected, recovery_model, has_bulk_logged_data, is_snapshot, is_readonly, is_single_user, has_backup_checksums, is_damaged, begins_log_chain, has_incomplete_metadata, is_force_offline, is_copy_only, first_recovery_fork_guid, last_recovery_fork_guid, fork_point_lsn, database_guid, family_guid, differential_base_lsn, differential_base_guid) values (N'{2F698A52-87F3-4332-B018-53995ED1158B}', @media_set_id, 1, 1, 1, 1, 1, 1, 1, 4608, @param2, @param3, 9, 0, 4262, -32, 0, 1183320000085670100001, 1183320000139093000001, 1182952000017510200234, 1183320000137682700001, convert(datetime,'2010-10-24 01:20:27.000',121), convert(datetime,'2010-12-08 01:30:41.000',121), convert(datetime,'2010-12-08 01:30:52.000',121), N'L', 52, 0, 90, 611, 275120128, @param4 , @param5, @param6, 512, 1033, 196609, @param7, 0, 'FULL', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'{1550FA46-902C-4C7D-9B34-21A6F79B85CE}', N'{1550FA46-902C-4C7D-9B34-21A6F79B85CE}', NULL, N'{2E903742-0662-49EA-9D5F-BFCA91F9FA22}', N'{777ED732-F315-4974-A1D5-3E3F23EF6D09}', NULL, NULL)
select @media_set_id = media_set_id from msdb.dbo.backupmediaset where media_uuid = N'{9F838F0C-9FE2-41A0-978F-2413917C58D3}'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply