Find and Manage SQL Server Index Fragmentation

Fragmentation takes place when indexes have pages, where the logical ordering inside the index, which is based on its key value, differs from the physical ordering inside the pages of the index.

This happens when the database engine modifies indexes when insert, update, and delete operations are performed on the underlying data. These modifications take place automatically and cause fragmentation over time, as the information in the indexes becomes more scattered in the database.

As a result of continuous fragmentation, with extra I/O required to locate the data that the index points to, query performance deteriorates. This is where our solution comes into play.

Database Index Defragmentation Tool for SQL Server

In order to solve this problem, it is necessary to start with analyzing the index and evaluating the degree of its fragmentation. Your best assistant here is our SQL Defrag feature, seamlessly integrated with dbForge Studio for SQL Server. In order to begin, proceed to Manage Index Fragmentation either from the Database menu or from the required database instance.

SQL Index Manager: Defrag Database for SQL Server

Defragmentation Process Options

After the required and database are selected, you can proceed to Options and quickly set up fragmentation thresholds, minimum index size, and index rebuild options.

SQL Defrag: Options

Scripting changes

dbForge Studio for SQL Server provides you with tools to fix indexes visually as well as by means of generating T-SQL scripts. You can either script changes immediately or save your scripts for future use.

SQL Server Defrag Index: Scripting changes

Exporting scan results to a CSV file

After each scan, you can save the results as a CSV file, which can be used for further analysis of index fragmentation or for reporting purposes.

Defragmentation of SQL Server Database: Exporting scan results to a CSV file

Analyzing SQL indexes via the Command Line

The Command Line offers a number of actions that you can run, making index fragmentation management even more flexible.

  • Use CLI to generate PowerShell automation scripts
  • Save your scripts in command-line execution files (.bat) to either schedule their execution via Windows Scheduler or simply run them whenever you need
  • Configure your preferred settings and save them for future use
SQL Defrag: Analyzing SQL indexes via the Command Line

Download your free 30-day trial of dbForge Studio for SQL Server to see its capabilities for yourself. All of its features, including Index Manager, were created to make work easy and enjoyable for you. To start analyzing SQL indexes, feel free to refer to our Documentation.