Identifying and fixing index fragmentation using SQL

You can also identify and fix index fragmentation using SQL.

 To check the index density of a table
  1. In the left pane, click app_profiling - DBA, and then choose File » Open Interactive SQL.

    Interactive SQL starts and connects to the app_profiling.db database.

  2. In Interactive SQL, run the following SQL statements to test the index density on the Employees table:

    CALL sa_index_density( 'Employees' );

    Density values range between 0 and 1. Values closer to 1 indicate little index fragmentation. Values less than 0.5 indicate a level of index fragmentation that may impact performance.

    Note

    The values for the indexes on the Employees will appear to show fragmentation issues because the values in the Density column of the results are well under 0.5. However, these numbers are artificially low due to the fact that the table is very small.

  3. In Interactive SQL, run an ALTER INDEX ... REBUILD statement similar to the following to improve the density of an index:

    ALTER INDEX PRIMARY KEY ON Employees REBUILD;
 See also