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 index fragmentation

 

Lesson 2: Identifying and fixing index fragmentation using Interactive SQL

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

Prérequis

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Diagnosing index 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

  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 the index density on the Employees table:

    CALL sa_index_density( 'Employees' );

    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 can impact performance.

    Note

    The values for the indexes on the Employees appear to show fragmentation issues because the values in the Density column of the results are well under 0.5. However, these numbers are artificially low because the table is very small.

  5. In Interactive SQL, execute the following ALTER INDEX...REBUILD statement to improve the density of an index:

    ALTER INDEX PRIMARY KEY ON Employees REBUILD;

  6. Close Interactive SQL.

Résultat

You have used Interactive SQL to identify and fix index fragmentation.

 See also
Copyright © 2013, SAP AG ou société affiliée SAP - SAP Sybase SQL Anywhere 16.0