SQL Server 2008 Optimize for Ad Hoc Workloads

SQL Server will create a plan for each batch you execute. If that plan is deemed to be "safe", it's stored in the plan cache for re-use. However, you use a lot of ad hoc queries only once, you will end up with a lot of cached plans that are really never re-used and just waste memory. Considering that plans have a higher priority in the cache, this could be relevant.

SQL Server 2008 has a new option to improve on that. It basically will only cache an ad hoc query on the second use. On the first run, it will create a "compiled plan stub" but it will not cache the plan itself. On the second time it sees that plan, it will cache it.

To enable this feature, use these commands (in a test system only):

SP_CONFIGURE 'show advanced options',1

SP_CONFIGURE 'optimize for ad hoc workloads',1

The second run will need to compile the ad hoc plan again (compared to the previous behavior), but it will save memory if they are only used once. For plans that are used many times, the extra hit is just compiling twice instead of once.

In any case, do look to verify if you have a lot of those ad hoc plans used only once. You can do this on a test system by enabling the option, running your workload and then checking to see if you have many of those "compiled plan stubs" left behind using this query:

SELECT * FROM sys.dm_exec_cached_plans
WHERE cacheobjtype LIKE 'Compiled Plan%'

In SQL Server 2008, they will show as cached object type "Compiled Plan Stu". In SQL Server 2008 SP1, this shows correctly as "Compiled Plan Stub". 

Your best strategy, as usual, is to use stored procedures with parameters. They are more likely to yeild stable plans that can be safely re-used. It might be important to look into why you're getting those ad hoc plans that are not consistent and rework your code...

Check a few links on this subject: