Tutorial: Diagnosing index fragmentation

Use this tutorial to learn how to use the Application Profiling Wizard to determine if your database has unacceptable levels of index fragmentation.

Introduction

When an index is created, table data is read and values for the index are recorded on index pages following a logical order. As data changes in the table, new index values can be inserted between existing values. To maintain the logical order of index values, the database server may need to create new index pages to accommodate existing values that are moved. The new pages are not usually adjacent to the pages on which the values were originally stored. This cumulative degradation in the order of index pages is called index fragmentation.

A symptom of index fragmentation is when commonly executed queries start to take longer to perform, particularly on tables where large blocks of rows are continuously being inserted, updated, and deleted.

Note

This tutorial assumes you have followed the setup steps for the application profiling tutorials. If you have not, see Setting up for the application profiling tutorials.

Preparation

Tip

You can copy and paste the SQL statements in this tutorial into Interactive SQL.

To set up index fragmentation

Start Sybase Central and connect to the test database app_profiling.db with the user ID DBA and the password sql.

If you have not created the test database, see Setting up for the application profiling tutorials.

If you are unfamiliar with starting Sybase Central and connecting to a database, see Connecting to a database on your own computer from Sybase Central or Interactive SQL.
In the left pane, select the app_profiling - DBA database, and then choose File » Open Interactive SQL.

Interactive SQL starts and connects to the app_profiling - DBA database.

In Interactive SQL, run the following SQL statements to introduce index fragmentation. These statements can take a few minutes to complete.

CREATE TABLE fragment ( id INT );
CREATE INDEX idx_fragment ON fragment ( id );
INSERT INTO fragment SELECT * FROM sa_rowgenerator ( 0, 100000 );
DELETE FROM fragment WHERE MOD ( id, 2 ) = 0;
INSERT INTO fragment SELECT * FROM sa_rowgenerator ( 0, 100000 );
INSERT INTO fragment SELECT * FROM sa_rowgenerator ( 0, 100000 );
COMMIT;

Exit Interactive SQL.

Analyzing the data

Use this procedure to identify index fragmentation and to learn where to look for index fragmentation warnings. It is recommended that you periodically check for fragmentation warnings on your production database.

Note

The statements you executed in the previous procedure introduced index fragmentation. However, on some systems there might not be enough fragmentation to result in the warnings and recommendations described in this procedure.

To use the Application Profiling Wizard to identify index fragmentation

In Sybase Central, choose Mode » Application Profiling.

If the Application Profiling Wizard does not appear, choose Application Profiling » Open Application Profiling Wizard.
On the Welcome page, click Next.
On the Profiling Options page, select Overall Database Performance Based On The Database Schema, and then click Next.
On the Analysis File page, in the Save The Analysis To The Following File field, type C:\AppProfilingTutorial.
Click Finish.

A list of recommendations appear in the Application Profiling Details pane.
To view more detail, double-click the Fragmented Indexes. A Recommendation window appears containing a SQL statement you can run to resolve the index fragmentation.

More information

To periodically check the density of indexes for a table, run the sa_index_density system procedure. 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.

In Interactive SQL, run the following SQL statement to view the index fragmentation introduced to the fragment table during this tutorial:

CALL sa_index_density( 'fragment' );

TableName	TableId	IndexName	IndexId	IndexType	LeafPages	Density
fragment	736	idx_fragment	1	NUI	1,177	0.597509

Your results might be different, but the Density column value should be approximately 0.6.

In Interactive SQL, run the following SQL statement to improve the density of the index:

ALTER INDEX idx_fragment ON fragment REBUILD;