Is Optimizing for AdHoc Queries Worth It?

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.