Lesson 2: Identifying and fixing index fragmentation using SQL

You can also identify and fix index fragmentation using SQL.

In the left pane, click app_profiling - DBA, and then click File » Open Interactive SQL.

Interactive SQL starts and connects to the app_profiling.db database.
In Interactive SQL, execute the following SQL statement 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.
In Interactive SQL, execute the following ALTER INDEX...REBUILD statement to improve the density of an index:
ALTER INDEX PRIMARY KEY ON Employees REBUILD;