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.

Posted on April 6, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: