Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 6.5: Index Statistic Details

By Alexander Chigrik,



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:

  1. From the Microsoft SQL Server 6.5 program group, double-click the   SQL Enterprise Manager icon.
  2. From the Server Manager window, select a server.
  3. In the Server Manager window, open the Databases folder and choose   database (pubs database, for example).
  4. From the Manage menu, choose Indexes.
  5. Choose appropriate table (authors, for example).
  6. Choose appropriate index (aunmind, for example).
  7. 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.
Total article views: 151 | Views in the last 30 days: 1
 
Related Articles
FORUM

HELP= Rebuild index & Update STATISTICS

HELP= Rebuild index & Update STATISTICS

FORUM

creating index and updating statistic

is it necessary to update statistic after creating an index ?

FORUM

Update Statistics Maint.plan Job

Update Statistics Maint.plan Job

ARTICLE

SQL Server 6.5: Index Statistic Details

This article examines how index statistics are used in SQL Server 6.5

FORUM

Statistics update

Statistics update

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones