As you probably know, there is a server configuration option called “optimize for ad hoc workloads”. For more details on that option check out the MSDN article. The real question is, how do I know the extent to which my environment uses AdHoc queries? Well, as usual there is a DMV for that. Use the query below to get an idea of how many plan caches are being stored but only used once. The number changes often, so it’s a good idea to baseline your typical number. For instance, I’ve created a SQL Agent job to record the number to a table every ten minutes. If you maintain high numbers then it might be a good idea to optimize for ad hoc workloads. As always, however, I would advise testing thoroughly first. Happy hunting.
USE Diagnostics INSERT INTO Diagnostics.dbo.AdHocTracking SELECT COUNT (*) FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc' AND usecounts = '1'
Credit to the Red Gate blog for my initial background information.