Will DBCC Reindex do the update statistics?

Question

Will DBCC Reindex do the update statistics?

sivaprasad

SSCertifiable

Points: 6183
More actions
April 25, 2005 at 5:29 am

#85954

Can anyone confirm if Will DBCC Reindex do the update statistics ?
Please explain if Yes, how come it perform the Update statistics if not why not this "DBCC Reindex" not running the "Update Statistics"
Thanx
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

It will update stats on the indexed columns but not any explicit or auto-generated stats on non-indexed columns.

If you want to be sure of getting those updated issue a sp_updatestats or update statistics for the individual stats/tables.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

sivaprasad SSCertifiable Points: 6183 More actions · Answer 2

Colin,

This is my understanding from your reply.

DBCC Reindex would update statistics of Indexed columns only.

If there are manually / auto created statistics exists on non-indexed column those statistics are unaffected. These statics can be updated using 'sp_updatestat' or 'Update statistics'

Is that right?

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 3

What Happens is that DBCC DBREINDEX is usually employed with tableName and indexName to pick and choose what to rebuild (you omitted how was this being run )

therefore if you do something like this

for each index ...

DBCC DBREINDEX ('Databasename.owner.tablename','indexname')

then the other columns statistics won't be updated but

if you run it like:

DBCC DBREINDEX ('Databasename.owner.tablename') -- no index specified

Everything is updated

you can double check that by running this after the rebuild:

SELECT i.name [Index],

STATS_DATE(i.id, i.indid) LastChange

FROM sysobjects o

join

sysindexes i on o.id = i.id

WHERE

o.name = 'YourTableName'

hth

* Noel

Green Armchair Hall of Fame Points: 3561 More actions · Answer 4

I have found occasions where dbcc dbreindex ('tablename') doesn't update all the statistics - I didn't get to the bottom of it, so to be safe I always run dbcc dbreindex followed by update statistics

sivaprasad SSCertifiable Points: 6183 More actions · Answer 5

Will DBCC Reindex update system tables? If not why?

Anyone could explain on this...

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

EdVassie SSC Guru Points: 60472 More actions · Answer 6

I recently ran some tests with DBREINDEX, using SQL 200 SP3a Hotfix 928. I do not know if the results would be different at a different fix level. I found that:

a) DBREINDEX (dbname..tablename, indexname) will only rebuild the named index and will only update statistics for the named index.

b) DBREINDEX (dbname..tablename) will rebuild all indexes, and will update stats, including column stats, for all indexes ONLY IF a cluster index is defined for the table. If no cluster index is defined, no stats are updated.

c) DBREINDEX gives an error message if you try to reindex a system table.

d) DBREINDEX reports successful completion but does absolutely nothing if run against a table with no indexes.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara