Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Database Performance » Application profiling tutorials


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.


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.


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.


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

To set up table fragmentation

  1. 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.

  2. 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.

  3. In Interactive SQL, run the following SQL statements to introduce table fragmentation:

    1. Create the table:
      CREATE TABLE "DBA"."tablefrag" (
      "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 ) );
    2. Create a procedure to insert values into the table:
      CREATE PROCEDURE "DBA"."proc_tablefrag"( ) 
            DECLARE i INTEGER; 
            SET i = 0; 
            WHILE i < 1000 
                  INSERT INTO "DBA"."tablefrag" ( "val1" ) 
                  SET i = i + 1; 
               END LOOP; 
    3. Insert values:
      CALL proc_tablefrag( );
    4. 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';
    5. Commit the changes you made to the database:
  4. 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.


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

  1. In Sybase Central, choose Mode » Application Profiling.

    If the Application Profiling Wizard does not appear, choose Application Profiling » Open Application Profiling Wizard.

  2. On the Profiling Options page, select Overall Database Performance Based On The Database Schema.

  3. On the Analysis File page, save the analysis file in the appropriate directory. For example, C:\AppProfilingTutorial.

  4. Click Finish.

    A list of recommendations appear in the Application Profiling Details pane.

    Fragmented Tables appears as one of the recommendations.
  5. 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:



See also