August 1, 2011 at 11:53 am
Greg Edwards-268690 (8/1/2011)
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.
Sounds like the most obvious culprit. I have no issue with "my" sql agent.
Can you run the query on your system to see what comes up? The more data we have the better chances we have to find out.
My server is somewhat short on memory so it's possible that the plans are aging out real fast out of the cache.
August 1, 2011 at 12:03 pm
I see many of these same "MSDB" single-use plans on test boxes where Commvault does not run backups. In general we're on Sql Server 2005 Enterprise SP4. Production and one test box are 64-bit, the rest 32-bit.
August 1, 2011 at 12:54 pm
Those messages are definitely SQL Agent updating the data it uses for its reports and such.
Do you have a lot of jobs running? Or a few jobs running very, very frequently?
If so, do they set connection properties when they run?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2011 at 1:21 pm
We do have a job that runs every minute deleting expired sessions from a database that holds session state info. Not sure what you mean by "set connection properties" The one I mentioned runs a stored procedure, via the sql agent service, that does the cleanup.
August 1, 2011 at 9:23 pm
Indianrock (8/1/2011)
Some have suggested setting forced parameterization on in MSDB to help with this. Any downsides to that?
It's worked fine for me in the past. I also wouldn't be terribly nervous about using FLUSHPROCINDB(4) if that suits you better.
August 2, 2011 at 5:56 am
SQLkiwi (8/1/2011)
Indianrock (8/1/2011)
Some have suggested setting forced parameterization on in MSDB to help with this. Any downsides to that?It's worked fine for me in the past. I also wouldn't be terribly nervous about using FLUSHPROCINDB(4) if that suits you better.
Now comes in the irony.
Put that in a job and let it run maybe every hour!
😉
I don't know about you all but I would consider getting the DB_ID from the name of msdb rather than hardcoding 4. I find it rather unlikely it changes during a restore but better safe than sorry!
August 2, 2011 at 9:08 am
Sounds good to me. I'll look into getting Change Control approval for using the flushprocindb function in a sql agent job to be run at least daily.
DECLARE @db_id int;
SET @db_id = DB_ID(N'MSDB');
PRINT @DB_ID
DBCC FLUSHPROCINDB(@DB_ID)
August 2, 2011 at 9:14 am
Daily sounds really long to me.
Maybe take 1-2 days to run the code manually and then see how big / fast the cache gets full again.
This code should run under 1-2 sec so I don't see any good reason to not run it more often than not.
August 31, 2012 at 5:21 am
As blow result, it showing too much single use plan in TeleData Database, Should I change database setting FORCED parameterization for reduce the single use plan?
sum_SizeInBytes_MBCnt_plansDB
1452.474777 53495 TeleData
12.35649872 103 master
5.4453125 45 NULL
4.328704834 198 msdb
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply