August 1, 2011 at 9:31 am
Some have suggested setting forced parameterization on in MSDB to help with this. Any downsides to that?
The other suggestion was to use DBCC FLUSHPROCINDB(4) which I thought only worked on SQL 2008 but did work on SQL 2005. On one test box running flushprocinDB on both MSDB and one other repository database for Quest Spotlight reduced these single-use plans by quite a bit. I'm going to turn on forced parameterization on both of these and see what happens after a few days.
August 1, 2011 at 9:48 am
What plans do you see in cache for msdb? Maybe you could change some of you jobs to use sp_executesql with parameters instead of exec().
That could cut it down by much for that db.
Thanks for the flush hint, I didn't know it worked in 2k5!
August 1, 2011 at 9:54 am
The MSDB plans are generated by the system -- I believe when various sql agent jobs start etc so doubt I can affect them in any way. The flushprocinDB is undocumented so we shouldn't rely on it long term.
Samples of the single-use "MSDB" plans
DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20110801, 41300) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 423 AND job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5
BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome = 1, last_run_duration = 0, last_run_retries = 0, last_run_date = 20110801, last_run_time = 41200 WHERE (job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5) AND (step_id = 1) DECLARE @lastExecuteStepDate DATETIME set @lastExecuteStepDate = msdb.dbo.agent_datetime(20110801, 41200) UPDATE sysjobactivity SET last_executed_step_date = @lastExecuteStepDate, last_executed_step_id = 1 WHERE job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5 AND session_id = 423 COMMIT TRAN
DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(20110801, 41200) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5 and session_id = 423
UPDATE msdb.dbo.sysjobservers SET last_run_date = 20110801, last_run_time = 41100, last_run_outcome = 1, last_outcome_message = N'The job succeeded. The Job was invoked by Schedule 59 (ASPState_JobSchedule_DeleteExpiredSessions). The last step to run was step 1 (ASPState_JobStep_DeleteExpiredSessions).', last_run_duration = 0 WHERE (job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5) AND (server_id = 0)
DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20110801, 41200) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 423 AND job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5
BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome = 1, last_run_duration = 0, last_run_retries = 0, last_run_date = 20110801, last_run_time = 41100 WHERE (job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5) AND (step_id = 1) DECLARE @lastExecuteStepDate DATETIME set @lastExecuteStepDate = msdb.dbo.agent_datetime(20110801, 41100) UPDATE sysjobactivity SET last_executed_step_date = @lastExecuteStepDate, last_executed_step_id = 1 WHERE job_id = 0x6B5393568E558E49A8D1F079BEB4B3B5 AND session_id = 423 COMMIT TRAN
August 1, 2011 at 10:01 am
How much space in the plan cache are you wasting on those plans? This might be worth it to create a connect case with ms so they fix it.
August 1, 2011 at 10:21 am
On our production system I wouldn't be surprised if the MSDB single-use plans are occupying half of the 7.5GB plan cache. This morning I noticed about 40k plans in cache, of which 35k are single-use and many seem to be MSDB/Agent related. We just turned on forced parameterization on our primary client database to get rid of thousands of unparameterized single-use inserts coming in from a scanning operation, so now we need to root these out.
The issue may have more to do with sql having to slog through thousands of plans to find the ones it needs for "real" work.
August 1, 2011 at 10:24 am
I think this is worth posting a connect item on this to MS.
What query did you use to find the issue?
August 1, 2011 at 10:35 am
I've never posted anything on MS Connect. Here is the query:
select usecounts, size_in_bytes, cacheobjtype, objtype, substring(st.text,charindex('select ',st.text),1800) as selectText,st.text, qp.query_plan
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
where st.text not like '%sys.dm_exec%'
order by usecounts
August 1, 2011 at 10:57 am
I changed the qry to this to aggregate it better.
select
SUM(cp.size_in_bytes) / 1024 / 1024 As sum_SizeInBytes_MB
, COUNT(*) As Cnt_plans
, DB_NAME(st.dbid) AS DB
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.usecounts < 3
GROUP BY
DB_NAME(st.dbid)
order by
sum_SizeInBytes_MB DESC
Can you confirm that most of the queries had to do with jobs and that this wasn't done by something like bakcupexec or any other 3rd party app?
August 1, 2011 at 11:04 am
We do have commvault running transaction log backups every 15 minutes in production, but I see many of the same plans on a test box that only runs nightly maintenance via sql agent.
Got this on that query
Arithmetic overflow error converting expression to data type int.
August 1, 2011 at 11:08 am
Indianrock (8/1/2011)
We do have commvault running transaction log backups every 15 minutes in production, but I see many of the same plans on a test box that only runs nightly maintenance via sql agent.Got this on that query
Arithmetic overflow error converting expression to data type int.
That might be the problem. I have 100s of jobs running daily and I don't have that issue on my system (and we're not under memory pressure so I know the cache is not being wiped out for no good reason).
Try this version :
SELECT
CONVERT(DECIMAL(18,2), SUM(CONVERT(BIGINT, cp.size_in_bytes)) / 1024.0 / 1024) AS sum_SizeInBytes_MB
, COUNT_BIG(*) As Cnt_plans
, DB_NAME(st.dbid) AS DB
FROM
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.usecounts < 3
GROUP BY
DB_NAME(st.dbid)
ORDER BY
sum_SizeInBytes_MB DESC
August 1, 2011 at 11:11 am
casting the bytes as float seems to work. Breaking plan cache down by DB is always sketchy.
select
SUM(cast(cp.size_in_bytes as float))/1024/1024 As sum_SizeInBytes_MB
, COUNT(*) As Cnt_plans
, DB_NAME(st.dbid) AS DB
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.usecounts < 3
GROUP BY
DB_NAME(st.dbid)
order by
sum_SizeInBytes_MB DESC
Results: usecounts<3
sum_SizeInBytes_MBCnt_plansDB
4750.484375 38186 NULL
12.703125 3 master
6.25 22 msdb
1.671875 6<main client database>
0.359375 1 Admin
0.0703125 1 ASPState
August 1, 2011 at 11:14 am
Ya that's what I was afraid of.
Running the same query with cross apply all dbs will literally kill the server.
I don't see the point of running any more tests. I'd call your vendor and tell them about your problem. The fix is real easy... just use option recompile and the problem goes away.
No point in getting MS into the mix with this, it's not their fault and they have no way of fixing it...
August 1, 2011 at 11:25 am
Commvault runs backups but doesn't interact with the sql agent at all -- I can see the sql backup commands it executes but don't see why it would have anything to do with statements like those below. ( Actually these are from the test box where commvault doesn't run, just a few sql agent jobs )
BEGIN TRAN UPDATE msdb.dbo.sysjobsteps SET last_run_outcome = 1, last_run_duration = 0, last_run_retries = 0, last_run_date = 20110801, last_run_time = 95300 WHERE (job_id = 0x3B642642610ABD4BB876FE977E126EF4) AND (step_id = 1) DECLARE @lastExecuteStepDate DATETIME set @lastExecuteStepDate = msdb.dbo.agent_datetime(20110801, 95300) UPDATE sysjobactivity SET last_executed_step_date = @lastExecuteStepDate, last_executed_step_id = 1 WHERE job_id = 0x3B642642610ABD4BB876FE977E126EF4 AND session_id = 374 COMMIT TRAN
DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20110801, 84400) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 374 AND job_id = 0x3B642642610ABD4BB876FE977E126EF4
DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(20110801, 92900) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = 374 AND job_id = 0x3B642642610ABD4BB876FE977E126EF4
UPDATE msdb.dbo.sysjobservers SET last_run_date = 20110801, last_run_time = 100700, last_run_outcome = 1, last_outcome_message = N'The job succeeded. The Job was invoked by Schedule 62 (ASPState_JobSchedule_DeleteExpiredSessions). The last step to run was step 1 (ASPState_JobStep_DeleteExpiredSessions).', last_run_duration = 0 WHERE (job_id = 0x3B642642610ABD4BB876FE977E126EF4) AND (server_id = 0)
August 1, 2011 at 11:27 am
I've called in more hands to test this query. My system's executing 1000+ jobs per day and I don't anything in the cache. There must be something different that's masking the issue.
August 1, 2011 at 11:49 am
Drop a note whether or not this ends up being a Commvault issue.
As part of a data center consolidation, they plan on moving to this product.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply