December 2, 2014 at 2:05 pm
Hi,
I am working on SQL Server(Standard 2008 R2 SP2) tuning project and have come across with SQL Cache memory size. i have two production servers and one is showing cache memory size 32MB and other which is more active and highly used by clients is showing 2MB and these figures remain same throughout the day.
I have no clu how much should be. SQL Server is running with 32GB of memory. during the tuning investigation, i did notice that there are some jobs which are schedule to run through out the day takes time to execute and in sp_who2 update sysjobactivities views came across. i run this command on server and got following figures.
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
-----------
my client databases and MSDB has hight Cnt_Plans, so i decided to run following bit of code as i found online suggesting to schedule it twice a day.
DECLARE @db_Id int;
SET @db_id = db_id('MSDB');
DBCC FLUSHPROCINDB(@db_ID)
do i just need to fluch msdb only or any other database as well where high number of count?
and also do i have to do the above stuff and what about setting up msdb to forced parameterized and also higher the SQL Cache memory means better the performance or less the cache memory is better ?
December 3, 2014 at 1:40 am
Don't flush the plan cache. All you're doing with that is forcing SQL to do more work to recreate the plans. You're going to be hindering performance by doing that, not helping.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2014 at 4:55 am
oh, i thought that will help to clear the cache ... one of online post was though suggesting to schedule it twice a day..Thanks, i won't run that..
What about force paramertizing msdb ? and about Cache memory counter ?
December 3, 2014 at 5:20 am
Why?
MSDB shouldn't see heavy usage, not to the point of being a concern around size of cache plans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply