August 11, 2006 at 8:13 am
Does DBCC DBReindex automatically update statistics?
I'm running DBCC DBReindex for each table in the database followed by EXEC SP_Recompile for each table in the database.
Need to know if I should insert a step between these 2 processes to EXEC SP_UpdateStats ?
August 11, 2006 at 9:24 am
I'm assuming you don't have auto create / update turned on. None of the documentation I've seen seems to indicate that updating of stats occurrs during a DBCC DBREINDEX operation. Therefore I would say you probably would want to include a seperate step for updating table stats. However if it were me, I would do this before performing the reindex, not after.
Just my .02
February 8, 2008 at 8:15 am
Just in case someone else is looking for an answer to this (I know it's an old post).
According to MS (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx):
There are two distinct advantages of running DBCC DBREINDEX over DBCC INDEXDEFRAG:
• DBCC DBREINDEX rebuilds statistics automatically during the rebuild of the indexes; this can have dramatic improvements on workload performance.
• DBCC DBREINDEX can take advantage of multiple-processor computers and can be significantly faster when rebuilding large or heavily fragmented indexes.
February 8, 2008 at 1:06 pm
Yes and no. DBCC DBREINDEX does update statistics when executed, but it executes sp_updatestats and updates a sample of statistics on table/index.
But to get the best result for performance of query, you can execute
update statistics [tablename] with full scan.
SQL DBA.
February 8, 2008 at 2:11 pm
$sanjayattray (2/8/2008)
Yes and no. DBCC DBREINDEX does update statistics when executed, but it executes sp_updatestats and updates a sample of statistics on table/index.But to get the best result for performance of query, you can execute
update statistics [tablename] with full scan.
Wouldn't you need to do
UPDATE STATISTICS [tablename] with resample
in order to do what you are saying? I thought with fullscan provides the same behavior as SAMPLE 100 PERCENT (which is what I see when I run a trace using sp_updatestats and using Update Statistics with fullscan).
Matt
May 6, 2009 at 3:36 pm
mps_42 (2/8/2008)
$sanjayattray (2/8/2008)
Yes and no. DBCC DBREINDEX does update statistics when executed, but it executes sp_updatestats and updates a sample of statistics on table/index.But to get the best result for performance of query, you can execute
update statistics [tablename] with full scan.
Wouldn't you need to do
UPDATE STATISTICS [tablename] with resample
in order to do what you are saying? I thought with fullscan provides the same behavior as SAMPLE 100 PERCENT (which is what I see when I run a trace using sp_updatestats and using Update Statistics with fullscan).
According to BOL, WITH FULLSCAN and SAMPLE 100 PERCENT are equivalent, also according to BOL: "FULLSCAN cannot be used with the SAMPLE option".
I do this:
sp_MSforeachtable @command1="print getdate() print ''?'' UPDATE STATISTICS ? WITH FULLSCAN"'
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply