Monitoring Databases  «Prev  Next»
Lesson 9Viewing statistics
Objective View statistic information.

View Statistical Database Information

After creating statistics you occasionally will want to view statistics information. Usually you will do this to verify you have created statistics on a table and to check the distribution of your data. SQL Server has two commands to monitor statistics.

dbcc show_statistics

The command dbcc show_statistics will display statistics for an index or statistic group on a table. The syntax is:
DBCC SHOW_STATISTICS (table, index_or_statistic_group)

The command, dbcc show_statistics will display distribution information for the table.

stats_date

To find out when statistics were updated on a table or an index group, use the scalar function stats_date. The syntax is:
STATS_DATE(table_id, index_id_or_statistic_group_id)
To find out when statistics were updated on every table and every index/statistics group, use the following SQL:

SELECT 'Table Name' = o.name, 'Index Name' = i.name, 
    'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE  o.id = i.id
order by 1,2

The next lesson will cover how to use sqlmaint.exe.