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

Index Statistics in SQL Server 7.0

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 statistics.

SQL Server 7.0 can update distribution statistics automatically, so in most cases it is not necessary to update distribution statistics manually and you can rely on SQL Server decisions to update distribution statistics.

In this article, I want to tell you about distribution statistics, and about how you can view and update distribution statistics.

Distribution Statistics

In comparison with SQL Server 6.5, there are no distribution pages in SQL Server 7.0: distribution statistics are stored as a long string of bits across multiple pages in the same way image data is stored. The pointer to to this distribution data is stored in the statblob column in the sysindexes table.

The distribution statistics are used to determine which indexes to use when processing a query or it is not possible to use an index and the full table scan will be made.

SQL Server 7.0 can update distribution statistics automatically, so it is not longer necessary to run UPDATE STATISTICS statement to update statistics manually. The SQL Server decides to update distribution statistics automatically by analyzing the value in the rowmodctr column in the sysindexes table.

Now distribution statistics can store the statistics information not only for the first column in index and not only for the indexed columns. You can create the distribution statistics for unindexed columns with the CREATE STATISTICS statement.

You can view or change the automatic UPDATE STATISTICS setting with sp_autostats stored procedure. By default, SQL Server 7.0 will automatically update distribution statistics.

View Distribution Statistics

You can use DBCC SHOW_STATISTICS statement to display the current distribution statistics for the specified target on the specified table.

This is the syntax:

DBCC SHOW_STATISTICS (table, target)
where
table
is the table with which the index is associated.
target
is the name of the object (index name or collection) for which to display statistics information.

This is the example to view the 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 (from SQL Server Books Online):

UPDATE STATISTICS table
    [
        index
        | (statistics_name[,...n])
    ]
    [    WITH
        [
            [FULLSCAN]
            | SAMPLE number {PERCENT | ROWS}]
        ]
        [[,] [ALL | COLUMNS | INDEX]
        [[,] NORECOMPUTE]
    ]

This is the example to update distribution statistics for the aunmind index from the authors table in the pubs database:

USE pubs
GO
UPDATE STATISTICS authors aunmind
GO

For more information, see: UPDATE STATISTICS (T-SQL)

Notes

  1. If you set to not automatically recompute distribution statistics, then 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. After running TRUNCATE TABLE statement, the distribution statistics will also be deleted, so if you set to not automatically recompute statistics, then after adding new data, you should manually run UPDATE STATISTICS statement to recreate distribution statistics.
Total article views: 3219 | Views in the last 30 days: 1
 
Related Articles
FORUM

HELP= Rebuild index & Update STATISTICS

HELP= Rebuild index & Update STATISTICS

ARTICLE

SQL Server 6.5: Index Statistic Details

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

ARTICLE

SQL Server 6.5: Index Statistic Details

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

FORUM

Update Statistics Maint.plan Job

Update Statistics Maint.plan Job

FORUM

creating index and updating statistic

is it necessary to update statistic after creating an index ?

 
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