Blitz Result: Missing Index Found in the Plan Cache
SQL Server can use indexes to make queries run much faster, and while it’s executing queries, it tracks which indexes it wishes would have been around. We can query the DMVs to discover what indexes SQL Server recommends, and we can also check the plan cache to identify which queries needed the index.
This part of our SQL Server sp_Blitz script checks the plan cache looking for resource-intensive queries that show missing index warnings.
To Fix the Problem
You could just create the index – but often that causes more problems than it solves. With each additional index, we may slow down inserts/updates/deletes, make backups take longer, make index rebuilds take longer, and more.
Review indexes holistically by checking out sp_BlitzIndex.
Learning More About SQL Server Data Storage
Want to learn more? We’ve got video training explaining it! In our 90 minute video series How to Think Like the SQL Server Engine, you’ll learn:
- The differences between clustered and nonclustered indexes
- How (and when) to make a covering index
- The basics of execution plans
- What determines sargability
- How SQL Server estimates query memory requirements
- What parameter sniffing means, and why it’s not always helpful
It’s free. Go start watching it now.