Microsoft® SQL Server® 2012 Bible

597

Chapter 22: Maintaining the Database

22

Result (abridged):

index_id: 1

index_type_desc: CLUSTERED INDEX

avg_fragmentation_in_percent: 0.559173738569831

page count: 15201

avg_page_space_used_in_percent: 99.538930071658

index_id: 2

index_type_desc: NONCLUSTERED INDEX

avg_fragmentation_in_percent: 1.23915737298637

page count: 1614

avg_page_space_used_in_percent: 99.487558685446

Index Statistics

The usefulness of an index is based on the data distribution within that index. For

example, if 60 percent of the customers are in New York City, then selecting all customers

in NYC will likely be faster with a table scan than with an index seek. However, to fi nd the

single customer from Delavan, Wisconsin, the query defi nitely needs the help of an index.

The Query Optimizer depends on the index statistics to determine the usefulness of the

index for a particular query.

DBCC SHOW_STATISTICS reports the last date the statistics were updated and basic infor-

mation about the index statistics, including the usefulness of the index. A low density

indicates that the index is selective. A high density indicates that a given index node

points to several table rows and may be less useful than a low-density index.

To update the statistics for a specifi c table, use the UPDATE STATISTICS command. To

update the statistics on all the tables in the current database, use the sp_updatestats

system stored procedure. sp_updatestats procedure basically runs UPDATE STATISTICS

on all the tables in the current database. sp_updatestats stored procedure does not

unnecessarily update every statistics. It updates specifi c statistics only if enough data has

changed based on rowmodctr information in the sys.sysindexes compatibility view.

The following code updates the statistics for all the indexes on the Person.Contact table in

the AdventureWorks2012 sample database.

USE AdventureWorks2012;

EXEC sp_help 'Person.Person;

UPDATE STATISTICS Person.Person;

The following code updates the statistics for all the tables in the AdventureWorks2012

sample database.

USE AdventureWorks2012;

EXEC sp_updatestats;

c22.indd 597c22.indd 597 7/31/2012 9:24:30 AM7/31/2012 9:24:30 AM

http://www.it-ebooks.info