It’s often the case that you have several different databases running on the same server. A consequence of this is that no matter how optimal your individual database may be, another database on the server, running suboptimally, may affect the server’s resources, and this may impact the performance of your database. Remember, CPU, memory, and tempdb are shared across all the databases on the server. Now that you know about the importance of indexes on query performance, it makes sense to report on those databases with the most missing indexes, because they may be indirectly affecting the performance of your database
High I/O is one of the main reasons for poor performance and often this is caused by missing indexes. The following query will list the databases with most missing indexes.
SET TRANSACTION
ISOLATION LEVEL
READ UNCOMMITTED
SELECT
DB_NAME(database_id) AS
DatabaseName
, COUNT(*) AS [Missing Index Count]
FROM sys.dm_db_missing_index_details
GROUP BY
DB_NAME(database_id)
ORDER BY
[Missing Index Count] DESC
