Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sys.dm_exec_cached_plans : Total plans in the procedure cache and total memory size mobilized Expand / Collapse
Author
Message
Posted Monday, October 18, 2010 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 6:41 AM
Points: 2, Visits: 224
Hi,
I want to get the number of plans in cache procedure.

i launch Simultaneously two commands on my SQL Server 2005 and i have different results :

The 1st command :

SELECT COUNT(*) AS [Nb_Total_Plans]
,SUM(c.size_in_bytes)/(1024*1024) AS [Total_Cache_Size_Mo]
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) s

i have this results
Nb_Total_Plans ................ Total_Cache_Size_Mo
2255 ................................ 180


The 2nd command :

SELECT COUNT(*) AS [Nb_Total_Plans]
,SUM(c.size_in_bytes)/(1024*1024) AS [Total_Cache_Size_Mo]
FROM sys.dm_exec_cached_plans c

i have this result

Nb_Total_Plans ................ Total_Cache_Size_Mo
2262 ................................ 175


What is the best pratice to have the Total plans in the procedure cache and the total memory size of the plans ?

Thank for help

Post #1006049
Posted Tuesday, October 19, 2010 7:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 1,203, Visits: 3,171
Not sure why you concern it that much.

I do not think you REALLY can Simultaneously run them. So of course the results were different.
Post #1006960
Posted Tuesday, October 19, 2010 3:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:42 PM
Points: 463, Visits: 1,030
memory management is controled by sql server


when it requires it gets memory by deleting some plans from the cache

you can still release memory from plan cache by doing

dbcc freeproccache

but the case you described above is that both the queries are the same.

second one is best practise.




--SQLFRNDZ
Post #1007357
Posted Wednesday, October 20, 2010 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 6:41 AM
Points: 2, Visits: 224
Ok,

Thank
Post #1007531
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse