Problem Indexes
The Problem Indexes report highlights indexes that may be causing performance or maintenance issues. It identifies indexes with a low fill factor or indexes that have been disabled, allowing you to take corrective action.
Report Details for Problem indexes
The report displays the following information for each problem index:
- Database Name – The database where the index is located.
- Object Name – The table or view the index belongs to.
- Fill Factor – The current fill factor setting for the index.
- Status – Indicates if the index is disabled or active.
- Set Fill Factor 99 and Enable Script – A ready-to-run script that sets the fill factor to 99% and re-enables the index.
- Drop Script – A script to safely drop the index if it is no longer needed.
How to find problem indexes
- Low Fill Factor – A very low fill factor can lead to excessive fragmentation and wasted space. Consider adjusting fill factors for better performance.
- Disabled Indexes – These indexes are not being used. They may have been disabled for troubleshooting or during maintenance and could require review.
- Indexes with Drop Scripts – If an index is unused and unnecessary, dropping it may reduce overhead.
Recommended Actions
- Review indexes with unusually low fill factors to determine if a higher fill factor is more appropriate.
- Re-enable disabled indexes that are still needed for queries or maintenance.
- Remove unnecessary indexes using the provided drop script to reduce storage and maintenance costs.
- Regularly monitor problem indexes to ensure index settings align with workload and performance goals.
Getting Help from Steve and the Stedman Solutions Team
We are ready to help. Steve and the team at Stedman Solutions are here to help with your SQL Server needs. Get help today by contacting Stedman Solutions through the free 30-minute consultation form.