October 18, 2010 at 3:05 am
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
October 19, 2010 at 7:42 am
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.
October 19, 2010 at 3:31 pm
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.
October 20, 2010 at 2:51 am
Ok,
Thank 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply