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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Performance improvements, diagnostics, and monitoring » Application profiling tutorials » Tutorial: Diagnosing table fragmentation


Lesson 2: Identifying and fixing table fragmentation using Interactive SQL

Use Interactive SQL to identify and fix table fragmentation. You should periodically check for fragmentation on your production database.


This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Diagnosing table fragmentation.

This tutorial assumes that you have created the test database, app_profiling.db. See Tutorial: Creating a test database for the application profiling tutorials.

Ensure that there are no other connections to app_profiling.db.

  1. Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Interactive SQL.

  2. In the Connect window, complete the following fields to connect to the test database, app_profiling.db:

    1. In the User ID field, type DBA.

    2. In the Password field, type sql.

    3. In the Action dropdown list, select Start and connect to a database on this computer.

    4. In the Database file field, type C:\AppProfilingTutorial\app_profiling.db.

    5. In the Start Line field, type dbeng16 -x tcpip.

  3. Click Connect.

  4. In Interactive SQL, execute the following SQL statement to test for table fragmentation on the Employees table:

    CALL sa_table_fragmentation( 'Employees' );

    If the value in the segs_per_row (the number of segments per row) column is greater than 1.1, then table fragmentation is present. Higher degrees of fragmentation may negatively impact performance.

  5. In Interactive SQL, execute the following REORGANIZE TABLE statement to reduce table fragmentation:

  6. Close Interactive SQL.


You have used Interactive SQL to diagnose table fragmentation.

 See also