Working with statistics – Part 1

There are two ways to update statistics on tables in SQL Server;

  1. sp_updatestats – this updates all statistics on every system and user table in the current database. This uses a sample rate of 20,000.
  2. UPDATE STATISTICS – this gives you more granular control and allows you to set certain options.

For this post, I’ll be focusing on the second variation UPDATE STATISTICS.

1) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option of this statement will update column statistics only. Using the INDEX option will update index statistics only.

2) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

3) Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement, will  update only index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update any column statistics.

4) Reorganizing an index, for example using the ALTER INDEX … REORGANIZE statement, does not update any statistics.

TIP! – If you rebuild an index it will update the index statistics. If you rebuild an index by partition, it will not update statistics you will have to do that manually!

If you update stats on a filtered index it wasn’t updating?!

SELECT OBJECT_NAME(object_id), name, OBJECT_NAME(object_id) + name ,STATS_DATE(object_id, stats_id)
FROM sys.stats
WHERE object_id IN
FROM sys.objects
WHERE type = ‘U’)
AND OBJECT_NAME(object_id) = ‘<table1>’
AND name = ‘<index1>’

ALTER INDEX [<index1>] ON [dbo].[<table1>]
GO (Will NOT cause stats update)

ALTER INDEX [<index1>] ON [dbo].[<table1>]
GO (Will cause stats update)


Cardinality = number of rows