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 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:
Example A.
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)
Example B.
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.