SQL server cached plans

If you are using OpenQuery or sp_execute_sql a lot then your server is probably loaded with cached execution plans, for adhoc queries that will never be used again.

These two grate blogs deal with the issue nicely
http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries 
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ 


Turn on "Optimize for Adhoc use" in sql server settings. This means a plan will only be cached as a stub until it is used twice. This is much smaller, but you are still going to fill up your plan cache with these adhoc items.

Now setup a task to run to clear down these stubs.

DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)

IF @MB > 10
        BEGIN
                DBCC FREESYSTEMCACHE('SQL Plans')
                RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)
        END
ELSE
        BEGIN
                RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
                — Note: this is only a warning message and not an actual error.
        END
go

SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) asdecimal(12,2)))/1024/1024
        , @Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans