As part of one’s Database Management strategy, one of the primary tasks at hand is to keep queries running at peak performance. One of the most impactful ways to do this is to identify ‘Missing Indexes’.
Simply put, Indexes can allow the database to fetch data faster and therefore make queries more efficient.
So, it should come as no surprise when I say that one of the most common questions I hear in my role as a Database Performance consultant for Quest is, “Can your solution help me find missing indexes?”
Quest’s Foglight offers a couple of ways to find missing indexes.
(Click images to view)
- The Excessive IO Wait Advisory – Navigate to Foglight’s Performance Investigator (PI) dashboard. Select the ‘Advanced Analytics’ tab and the ‘Advisories’ tab. If Foglight identifies activity related to excessive IO wait, it is flagged and the advisory is generated.
In this particular example, no missing indexes were found.
If there were, the details would be displayed at the bottom.
- Foglight provides handy integration with Microsoft’s SQLServer Management Studio (SSMS). Initiated from Foglight, users can open the execution plan of a query. For this second option to find missing indexes, a nod goes to my Quest engineer teammate, Jason Hall, who recently pointed this out to me.
As with the prior example, navigate to Foglight’s Performance Investigator (PI). Select a query from the navigation tree on the left side. Select the ‘Analyze Plan’ button.
Then, choose the ‘Open in SSMS’ option in the top right corner.
When applicable, missing index information is noted in green.
Yet another way to identify missing indexes is discussed in this blog by Ed Pollack.
In the blog, Ed reviews Quest’s ApexSQL Defrag.
https://www.sqlshack.com/collecting-aggregating-analyzing-missing-sql-server-index-stats/
Visit www.quest.com for more information.
