
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

SQL Server 6.5: index statistics details

Alexander Chigrik
chigrik@mssqlcity.com

Introduction
Distribution Pages
View Index Statistics
Update Distribution Statistics

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 parts:

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 will 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, 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 result 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 result 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.

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc