SQL Server: How to Analyze Ad Hoc Queries Workload

Selecting an optimized execution plan, is a time consuming process, which query optimizer do for us, when ever a query is executed. If query is too complex then optimizer have to do a lot of work. And once the plan is found, it is cached in memory first, so it can be used on next execution of same query.

Most of the time, cached execution plan that were created for Ad Hoc queries can not be reused and if percentage of such cached plans that are used only once is comparatively high then it must be considered as an alarming situation for overall database performance.

Following query can be helpful for analyzing cached execution plans.

SELECT objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

Last Column “Total Plans - USE Count 1” shows total number cached plans in given category that are used only once.