Tutorial: Diagnosing table fragmentation

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

Introduction

Table data is stored on database pages. When Data Modification Language (DML) statements such as INSERT, UPDATE, and DELETE are executed against a table, rows might not be stored contiguously, or might be split between multiple pages. Even though CPU activity is high, table fragmentation can negatively impact the performance of queries that require a scan of the table.

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 table 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 table fragmentation:

Create the table:

CREATE TABLE "DBA"."tablefrag" (
"id" UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT, 
"val1" LONG VARCHAR NULL, 
"val2" LONG VARCHAR NULL, 
"val3" LONG VARCHAR NULL, 
"val4" LONG VARCHAR NULL, 
"val5" LONG VARCHAR NULL, 
"val6" LONG VARCHAR NULL, 
"val7" LONG VARCHAR NULL, 
"val8" LONG VARCHAR NULL, 
"val9" LONG VARCHAR NULL, 
"val10" LONG VARCHAR NULL,
PRIMARY KEY ( id ) );

Create a procedure to insert values into the table:

CREATE PROCEDURE "DBA"."proc_tablefrag"( ) 
   BEGIN 
      DECLARE i INTEGER; 
      SET i = 0; 
      WHILE i < 1000 
         LOOP 
            INSERT INTO "DBA"."tablefrag" ( "val1" ) 
            VALUES('a'); 
            SET i = i + 1; 
         END LOOP; 
   END;

Insert values:
CALL proc_tablefrag( );

Update the values in the table:

UPDATE "DBA"."tablefrag" 
SET "val1" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val2" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val3" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val4" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val5" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val6" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val7" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val8" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val9" = 'abcdefghijklmnopqrstuvwxyz0123456789', 
    "val10" = 'abcdefghijklmnopqrstuvwxyz0123456789';

Commit the changes you made to the database:
COMMIT;

Exit Interactive SQL.

Analyzing the data

Use this procedure to identify table fragmentation and how to locate table 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 table fragmentation. However, on some systems there might not be enough table fragmentation to result in the warnings and recommendations described in this procedure.

To use the Application Profiling Wizard to identify table 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 Profiling Options page, select Overall Database Performance Based On The Database Schema.
On the Analysis File page, save the analysis file in the appropriate directory. For example, C:\AppProfilingTutorial.
Click Finish.

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

More information

To check for table fragmentation (for example, CALL sa_table_fragmentation( 'tablefrag' );) run the sa_table_fragmentation system procedure . If the number of segments per row is greater than 1.1, then table fragmentation is present. Higher degrees of fragmentation may negatively impact performance. See sa_table_fragmentation system procedure.

The table you created in this tutorial should have a fragmentation value of approximately 1.9.

In Interactive SQL, run the following SQL statement to reduce table fragmentation:

REORGANIZE TABLE tablefrag;

See REORGANIZE TABLE statement.