I am a big fan of Glenn Berry’s SQL Server Diagnostic Information Queries. One of the queries identifies the top 25 “missing index warnings for cached plans in the current database.” (As of the October 2015 version, this is Query 61 in the SQL Server 2014 query file, and an identical query exists for each of the versions.) The query is as follows:

SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName], 
               query_plan, cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);

Note that the query executes a cross apply to sys.dm_exec_query_plan, which returns the context database ID associated with the plan, and the query then filters by database ID. So SQL Server must evaluate the query for all plans in the cache, and then return only the ones for the current database.

I have found that I sometimes want to run this for several databases, but it’s a lot of overhead to run this same query several times, each time filtering to the current database. I decided to do a rewrite so that I only need to run the query one time:

WITH MissingIndexWarnings AS
(
	SELECT	db.name [Database Name],
			OBJECT_NAME(objectid, dbid) AS [ObjectName], 
			query_plan, cp.objtype, cp.usecounts,
			row_number() over (partition by dbid order by cp.usecounts desc) rn
	FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
	inner join sys.databases db on qp.dbid = db.database_id
	WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
)
SELECT	miw.[Database Name],
		miw.ObjectName,
		miw.query_plan,
		miw.objtype,
		miw.usecounts
FROM MissingIndexWarnings miw
WHERE miw.rn <= 25
AND miw.[Database Name] in ('Database1', 'Database2', 'Database3')
ORDER BY miw.[Database Name], miw.usecounts DESC OPTION (RECOMPILE);

Obviously, the idea is to replace the list of database names in the WHERE clause. As a side benefit, this allows you to run the query for a database where the compatibility level is 80. The original query generates an error if executed in the context of an 80 database, but with the rewrite, you can switch to a database at a higher compatibility level (for instance, master) and filter to the desired database name.