She Can Dig It!

With today’s SQL Server instances, there easily be anywhere from a dozen to a hundred databases on a single SQL Server instance.  In fact, on my laptop, I currently have almost two dozen databases and these are just from working on projects. Setup a SharePoint server and add a few sites and you’ll easily see a few dozen databases crop up over night.

Regardless of the number of databases on an instance of SQL Server, we are still required to performance tune the queries that are running on the servers.  While we may only be responsible for a select number of databases, many of the mechanisms for storing performance statistics are scoped to the instance level versus the database level.  Two such dynamic management views (DMVs) are sys.dm_exec_cached_plans and sys.dm_exec_query_plan.  These views are important when you need to pull back all of the cached plans with their execution plans for a specific database.

To solve this dilemma, you can utilize the DMV sys.dm_exec_plan_attributes.  This DMV provides information on specific attributes for an execution plan.  One attribute is the dbid, or the database that the contains the objects that the plan was generated for.  With the query provided in Listing 1, you can leverage sys.dm_exec_plan_attributes to obtain a list of all execution plans for any database.

--Listing 1. List all execution plans for a specific database
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.usecounts
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
,pa.attribute
,pa.value
,pa.is_cache_key
,qp.dbid
,qp.objectid
,qp.number
,cp.plan_handle
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE attribute = 'dbid'
AND value = DB_ID()
ORDER BY cp.usecounts DESC

Summary

Today’s post was just a little script, but one that I often leverage when working on a limited set of databases for a client.  No sense reviewing more plans than necessary.  With this I can hone in on the trouble application and get to the answers sooner.  Do you see a use for doing this in your environment?  Or do you remember a time that you could have used a query like this?