August 14, 2010 at 6:50 pm
Hi All,
I am trying to understand the dm_exec_cached_plans contents and try to reduce the memory utilization. Please find my configuration details and problem.
System configuration:
OS: 32 BIT
SQL: sql 2005 sp3 standard edition
DB Size: 200 mb
Memory: 3.2 gb
SQL Min:500 mb
SQL Max:1200 mb
Quite often the memory utilization is 80%, when I looked at the cached_plans it has the same code segment. Typically the cached sql is for the entire database meaning all tables,views e.t.c. I used the below query to get the SQL and the size is roughly 300 mb. What'z the process which would run for the entire database and fills up half the memory in my case ? Is there a system process which would do regularly. Can you please help me to understand this process ?
SELECT (SUM(CAST(size_in_bytes AS MONEY)) / 1024 / 1024) AS [SizeMB],count(*),substring(text,1,100)
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where Objtype = 'Adhoc'
group by substring(text,1,100) order by 1 desc
When I ran the above query, I see the cached_sql.txt for the entire objects on the database.
Additional details is the DBCC memory status and from the SQL Server Default reports I see the component "CACHESTORE_SQLCP" which is the dominant one.
During high memory utilization I free the system cache and modify the max memory setting to 900 mb which would release 300 mb to the OS, else I should end up recycling the SQL Service. After the memory is released I go back and modify to 1200 mb.
Please correct me if any of my understanding is incorrect or what other things I should look in order for the memory utilization to go low.
Thanks,
Ganesh
August 15, 2010 at 12:00 am
My guess could be this link,
Let me check is some one scripting the whole database.
Thanks,
Ganesh
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply