For example, the statistics might lead the optimizer to choose an index seek over an index scan, a potentially more resource-intensive operation.
Transact-SQL Syntax Conventions is not specified, the query optimizer updates all statistics for the table or indexed view.
The DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in the database.
DBCC DBREINDEX is efficient over dropping and recreating indexes. You need to figure out the total disc space used by the index that uses the most disk space. Sir What do you mean by updating the statistics at the end after we run dbcc dbreindex. Sir What do you mean by updating the statistics at the end after we run dbcc dbreindex. Thanks But Dave, doesn’t a reindex also update stats? I am actually running update stats by itself once a week though auto update stats is on, it’s likely not required.
By changing the number of processors SQL Server can use in parallel, in other words the maximum degree of parallelism (MAXDOP), we can improve index rebuild performance.
This option is by default set to zero instance-wide on SQL Server, it does not mean use zero processors.
We have been asked to reduce our maintenance window for the SQL Server off-line index rebuilds and updating statistics in order to keep our systems online longer.