Jack Corbett (8/22/2008)
What build level are you at? I believe this is an issue related to procedure cache and that SP2 fixed it.
The proc cache pre SP2 could grow to about (iirc) half the size of the buffer pool, reducing the amount of memory available for the data cache and increasing the amount of time to find a cached plan. SQL still honoured the max memory setting, which limits the size of the total buffer pool.
I recall seeing my proc cache at 20GB on SP1, with SQL set to max memory of 42GB, total of 48GB on the box.
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