SQL Server 6.5: Index Statistic Details

,


Introduction

Sometimes it is difficult to determine which indexes to use when

processing a query. In this case, the query optimizer uses distribution

pages.

SQL Server 6.5 cannot update distribution statistics automatically

as SQL Server 7.0 can, so you should manually update distribution

statistics when a large amount of data in an indexed column has been

added, changed, or deleted.

In this article, I want to tell you about structure of the distribution

pages, about distribution step and index density, and about how you can

view and update distribution statistics.

Distribution Pages

There are five kinds of pages in SQL Server 6.5:

  • Data pages

  • Index pages

  • Allocation pages

  • Text/Image pages

  • Distribution pages

Every index can have only one distribution page. The distribution page

is used by query optimizer to determine which indexes to use when

processing a query, or to determine whether it is more efficient to use

the index or to scan the table.

The size of a distribution page is 2Kb, i.e. 2048 bytes, as well as the

size of other SQL Server 6.5 pages.

Every distribution page consists of three part:

  • 32 bytes header

  • Index density

  • Distribution step

Index density uses (n + 2) * 8 bytes, where n - is the number of fields

in the index. Other space is used to store the distribution steps.

View Index Statistics

There are two ways to view the index statistics in SQL Server 6.5:

  • With GUI interface from the Enterprise Manager

  • With DBCC SHOW_STATISTICS statement

To view the index statistics from the Enterprise Manager:

  • From the Microsoft SQL Server 6.5 program group, double-click the

      SQL Enterprise Manager icon.

  • From the Server Manager window, select a server.

  • In the Server Manager window, open the Databases folder and choose

      database (pubs database, for example).

  • From the Manage menu, choose Indexes.

  • Choose appropriate table (authors, for example).

  • Choose appropriate index (aunmind, for example).

  • Click the Distribution button.

You can use DBCC SHOW_STATISTICS statement to display the statistical

information in the distribution page for an index on a specified table.

This is the syntax:

DBCC SHOW_STATISTICS (table_name, index_name)

To view the index statistics with DBCC SHOW_STATISTICS statement, use

the following script (to view index statistics for the aunmind index

from the authors table in the pubs database):

USE pubs

GO

DBCC SHOW_STATISTICS (authors, aunmind)

GO

Update Distribution Statistics

To update distribution statistics, you can use UPDATE STATISTICS

statement. This is the syntax:

UPDATE STATISTICS [[database.]owner.]table_name [index_name]

where

table_name
is the table with which the index is associated.
index_name
is the index for which the distribution statistics

will be updated. If you not specify index_name

parameter, then the distribution statistics for all

indexes in the specified table will be updated.

Notes

  1. Because SQL Server 6.5 cannot update distribution statistics

    automatically as SQL Server 7.0 can, you should manually run

    UPDATE STATISTICS statement periodically (when a large amount

    of data in an indexed column has been added, changed, or deleted).

  2. The distribution pages will be created only when the index be

    created on the table with data in it, or when you manually run

    UPDATE STATISTICS statement on the table with data in it.

    When there are no records in the table, then there are no

    distribution pages.

    So, if you want to create the table from the script file, then

    create index only after you will insert the data into this table.

    See the examples below:

    Example1.

    The index was created before insert the data, so there is no

    distribution page.

    SET NOCOUNT ON

    GO

    if object_id('Table1') is not null drop table Table1

    GO

    CREATE TABLE Table1 (

    id int identity primary key,

    Field1 char(50)

    )

    GO

    CREATE INDEX indField1 on Table1 (Field1)

    GO

    DECLARE @i int

    SELECT @i = 1

    WHILE @i <= 1000

    BEGIN

    INSERT INTO Table1 VALUES (LTRIM(str(@i)))

    SELECT @i = @i + 1

    END

    GO

    DBCC SHOW_STATISTICS (Table1, indField1)

    GO

    This is the results set (there is no distribution page):

    Updated              Rows        Steps       Density

    -------------------- ----------- ----------- ------------------------

    NULL 1000 0 0.0

    (1 row(s) affected)

    All density Columns

    ------------------------ ------------------------------

    0.0 Field1

    (1 row(s) affected)

    Steps

    --------------------------------------------------

    (0 row(s) affected)

    Example2.

    The index was created after the data was added, so there is

    distribution page.

    SET NOCOUNT ON

    GO

    if object_id('Table1') is not null drop table Table1

    GO

    CREATE TABLE Table1 (

    id int identity primary key,

    Field1 char(50)

    )

    GO

    DECLARE @i int

    SELECT @i = 1

    WHILE @i <= 1000

    BEGIN

    INSERT INTO Table1 VALUES (LTRIM(str(@i)))

    SELECT @i = @i + 1

    END

    GO

    CREATE INDEX indField1 on Table1 (Field1)

    GO

    DBCC SHOW_STATISTICS (Table1, indField1)

    GO

    This is the results set (the distribution page was created):

    Updated              Rows        Steps       Density

    -------------------- ----------- ----------- ------------------------

    Feb 7 2001 10:41PM 1000 36 0.001

    (1 row(s) affected)

    All density Columns

    ------------------------ ------------------------------

    0.001 Field1

    (1 row(s) affected)

    Steps

    --------------------------------------------------

    1

    123

    149

    174

    2

    224

    25

    275

    30

    325

    350

    376

    400

    426

    451

    477

    501

    527

    552

    578

    602

    628

    653

    679

    703

    729

    754

    78

    804

    83

    855

    880

    905

    930

    956

    981

    (36 row(s) affected)

  3. After running TRUNCATE TABLE statement, the distribution pages

    will also be deleted, so after adding new data, you should manually

    run UPDATE STATISTICS statement to recreate distribution pages.

Rate

Share

Share

Rate