The problem of the procedure cache being inflated with ad-hoc SQL plans that are almost never reused has been discussed many times. You can find an interesting post about this by Tony Rogerson here. Another post by Lara Rubbelke that touches on the same problem is here.
Just to summarize: on systems that have a lot of ad-hoc SQL queries, the procedure cache can get rather large. That is especially true for some 64bit systems with a lot of memory as the procedure cache has higher thresholds for memory pressure there. The effect is usually the size of procedure cache being disproportionally large in comparison with the total system memory and the size of buffer pool used for data pages. I have personally seen systems with several GBs of memory used for procedure cache that was filled mostly with ad-hoc and seldom reused plans. I have even seen this occurring on systems that were using stored procedures exclusively, as there were still a lot of ad-hoc queries coming from SQL Agent engine as well as various parts of SQL Server management tools...
I could go for hours explaining the reasons why this problem occurs and different ways to solve it. I cover this topic to some extent in my "Dude, Where Is My Memory?" conference session (you can see this session at many conferences throughout Europe this fall, including TechEd ITForum). I am contemplating writing an article about this, but no promises at this point...
OK, back to our problem... How to check if your server is experiencing this problem? The easiest way is to run this simple query:
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
group by objtype
If you have problems with ad-hoc plans, you are likely to see several thousand of plans with objtype "Adhoc", occupying as much as several GBs (bulk of all memory consumed by the procedure cache) and having very low avg_use_count. The ad-hoc plans generally accumulate over time (up to the memory pressure limits of the procedure cache) so you may not see this right after a server restart or proc cache flush - but you will see the number constantly growing until it reaches the limit.
If you want to get rid of the ad-hoc plans, one simple and effective yet somewhat dangerous way is to clear the procedure cache completely with DBCC FREEPROCCACHE. I have seen many people do it, even having it scheduled as a regular job. It works great to get rid of all the clutter in procedure cache but the problem is that since you are clearing the cache completely, you are also getting rid of your valuable SP plans. This can cause a noticeable system slowdown right after, due to the cost of compiling any incoming SP call and possible contention in case of many compilations occurring at the same time (compilations in SQL Server are throttled). And of course you will have to do it again in a few hours/minutes as the ad-hoc plans will start building up again...
So, I recently started wondering how to be a little more selective in what we throw out of the procedure cache. The revelation came to me about a week ago and I already had a chance to test it on a few systems. The idea is so simple that I am actually very surprised that I have never seen it described anywhere before. The key to understanding how this works (and how I arrived at my solution) is to realize that there is no single procedure cache. The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:
CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the stuff that we are after.
CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.
With all this information, I am sure you can guess where I am going: Can we clear just one part of the procedure cache? Just the cache store holding the ad-hoc plans?
And the answer is: YES!
All you have to do is run:
DBCC FREESYSTEMCACHE('SQL Plans')
and you will see all your ad-hoc and prepared plans vaporize, while your SP plans will remain intact. This is much nicer than the overkill of DBCC FREEPROCCACHE and will have much lower performance impact on the system. And, in case you wonder, the parameter in the statement above is the name of the cache store of type CACHESTORE_SQLCP.
I am curious to hear your comments and whether this solution works for your scenario...