SQL Memory Usage - Further Research into the Procedure Cache

Following on from the last post on SQL Server Memory usage, I was particularly interested in the large use of the procedure cache by ad-hoc queries. In the example I have here, there is a large number of ad-hoc queries, and the cache is constantly growing.

This is a consistent behaviour, whenever a statement is executed, SQL Server will search through the cache to see if the statement has been used before. As this is an ad-hoc statement, it is unlikely to find one so will create a new plan and add it to the cache. These are not aged all that quickly, so the effect is that the cache keeps on growing, which in turn will slow down the process of locating a plan. This then means that SQL Server (if there is a demand on memory) may flush pages from data cache, which will also have an effect on the performance.

The preferred solution is to use parameterised stored procedures instead of ad-hoc queries, however if this is not possible (amending the application may be beyond your control) then flushing the stored procedure cache is the next best thing.

Ideally, we do not want to flush the whole cache, as genuine reuseable plans are a good thing. Clearing the ad-hoc plans is therefor preferred. This is not obvious from BOL, however I stumbled on the following statement on a google trawl :

DBCC FREESYSTEMCACHE('SQL Plans')

This frees just the ad-hoc plans - perfect!

It goes without saying that this needs to be tested, with proper performance figures to back it up. If you are short of memory, and/or are witnessing a slowdown through time on general SQL activity then this may be one of the solutions.