How to analyze diagnostics results

When at least one scheduled run in a diagnostics project is completed, you can begin to analyze the results. The general idea is to use these results to: 

• Remove unused indexes 
• Create the most important missing indexes 
• Rebuild or reorganize the most important fragmented indexes 

Finding unused indexes

To find unused indexes in the database(s) you analyzed, do the following steps: 

1. Click on Reports in the navigation menu.
2. Click on the Expand (+) icon next to the Diagnostics folder. 
3. Click on Diagnostics overview.
   
   
   
   
4. Select the diagnostics project you created from the Diagnostics dropdown. 
5. Select the schedule you created from the Diagnostics run dropdown. If you have multiple runs finished, you can select another run from the Compare to dropdown. This will enable you to see change in index status between two time points.
   
   
   
6. Click View Report. 
7. Click on Unused indexes inside the report.
   
   
   
   
8. Click on the database you want to analyze. A list of unused indexes is shown.
   
   
   

It is usually a good idea to confirm that an index really is unused by having several scheduled runs before removing indexes. 

Finding missing indexes

To find missing indexes in the database(s) you analyzed, do the following steps: 

1. Click on the Back arrow icon inside the report twice to return to diagnostics overview. 
2. Click on Missing indexes. 
   
   
   
   
3. Click on the database you want to analyze. A list of missing indexes is shown.
   
   
   

The report will by default show the first 20 indexes that don’t exist but would speed up queries. The indexes are sorted by their total impact, with the most important indexes listed first. In other words, if you create the first missing indexes on the list, it will have the most significant effect on the system. To filter out indexes with a low relative impact, use the Minimum relative impact % (0-100) parameter.  

For more information about missing index impact, see: https://technet.microsoft.com/en-us/library/ms345421(v=sql.105).aspx. 

Finding fragmented indexes 

To find fragmented indexes in the database(s) you analyzed, do the following steps: 

1. Click on the Back arrow icon inside the report twice to return to diagnostics overview. 
2. Click on Fragmented indexes. 
3. Click on the database you want to analyze. A list of fragmented indexes will be shown.
   
   
   

The report will by default show the first 20 indexes that are fragmented. To filter out indexes with a low page count, use the Minimum page count parameter. To filter out indexes with a low average fragmentation percentage, use the Minimum relative estimated fragmentation % parameter.