about the concern about sp_updatestats

Question

about the concern about sp_updatestats

892717952

SSC Eights!

Points: 843
More actions
June 24, 2021 at 1:02 am

#3899887

Do I still need to run sp_updatestats if I execute DBCC DBREINDEX(tablename) for tables in database?
I think you should still use to run sp_updatestats as dbcc dbreindex doesn't the statistics created by create statistics, is it right? thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 1

You don't have to update statistics after rebuilding indexes. When you rebuild an index SQL server also rebuilds it's statistics. Since it reads all the records in the table, the statistics will be based on all the records and will be very accurate. If you update the statistics after that, SQL server will read only a sample of the table and you'll have less accurate statistics.

I have to admit that I don't remember if SQL Server 2012 has the alter index statement. If it does, then you should consider using it instead of DBCC DBRINDEX statement which is going to be removed in the future.

Adi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

892717952 wrote:

Do I still need to run sp_updatestats if I execute DBCC DBREINDEX(tablename) for tables in database?
I think you should still use to run sp_updatestats as dbcc dbreindex doesn't the statistics created by create statistics, is it right? thanks!

You should stop using DBCC DBREINDEX and use ALTER INDEX REBUILD instead. It's been a part of SQL Server since at least 2005, IIRC.

Also, you're still going to need to rebuild some statistics... those would be the column statistics that aren't associated with any index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)