Analyze SQL indexes
Proper indexing is a critical aspect of database performance optimization. It can help speed up query execution and improve overall system performance. dbForge Index Manager can help you scan for index fragmentation and fix any issues found to optimize SQL indexes.
The guide provides general steps to analyze SQL indexes using dbForge Index Manager. To open the Index Manager, in SSMS Object Explorer, right-click the database you want to analyze and select Index Manager > Manage Index Fragmentation.
Scan multiple databases
You can choose a different server and a database to scan and fix index fragmentation. To do this, select the server connection and the database from the dropdown list on top of the Index Manager document.
Analyze the index fragmentation results
After scanning the database, the Index Manager: [database_name] document opens dividing all the indexes into two sections:
- Action required: This section displays all indexes that require maintenance based on the default index fragmentation analysis criteria.
- No action required: This section displays indexes that do not require maintenance based on the default index fragmentation analysis criteria.
Each section displays the following index details:
- Fix type, which is a recommended action to take on the indexes
- Index name
- Database object name to which the index belongs
- Type of the index either clustered or nonclustered
- Index size in pages and Mb
- Index fragmentation in %
- Partition number
- Rows in partition
- Reason for index maintenance
- Result of the fix action. When you click Fix, this column will inform you that the index has been successfully rebuilt or reorganized (depending on the fix type)
The action to fix can be:
| Fix Type | Description | Fragmentation |
|---|---|---|
| Rebuild | Deletes the old index and creates a new one from scratch. This eliminates fragmentation, compacts the pages based on the specified or current fill factor setting, and organizes the index rows in sequential pages. | > 30% |
| Reorganize | Scans through the current index and performs cleanup procedures to improve its performance | > 10% and <= 30% |
In the Action required section, you can perform the actions on all indexes by selecting the checkbox next to the section name or individual indexes by selecting the checkbox next to the specific index on the results grid.
Export index fragmentation results
You can export results as a .CSV file for further index fragmentation analysis and reporting. For more information, see Exporting scan results.
Configure index fragmentation thresholds
You can customize the default fragmentation thresholds in the Options dialog. To see the updated index fragmentation result, click Reanalyze. For more information, see Configure index fragmentation thresholds.