SQL Server 2005 : Procedure Cache memory usage keeps growing

We have had an issue with a clustered instance of SQL-Server 2005 whereby it would run out of memory. Several things were tried but didn't seem to make any difference.

The server in question was RTM, and due to changes in infrastructure could not be upgraded to SP2. Without it being SP2 we were not able to properly ascertain the issue.

A new installation was created and updated to SP2 (3050) and the majority of the applications were moved.

We have been monitoring the memory and so far the SP2 seems to have sorted the issue. Further investigation has highlighted an issue with SQL 2005 RTM whereby the procedure cache is not properly flushed. Our investigation seem to agree with this issue. The monitoring needs to over a few days but so far it is looking OK.

The SQL used to monitor the cache is as follows, we also used Idera to monitor visually :


-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'

-- Top 10 consumers of memory from Buffer Pool
SELECT TOP 10 type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts

-- Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC