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.

Prerequisites

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.

Task

Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Interactive SQL.
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.
Click Connect.
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.
In Interactive SQL, execute the following REORGANIZE TABLE statement to reduce table fragmentation:
REORGANIZE TABLE Employees;
Close Interactive SQL.

Results

You have used Interactive SQL to diagnose table fragmentation.

Lesson 2: Identifying and fixing table fragmentation using Interactive SQL

Task

See also