DBCC DBREINDEX and Statistics

In SQL Server 2000 when you create an Index, statistics are created with ful
lscan. My question is when you issue the DBCC DBREINDEX command, are statis
tics updated with a fullscan or the default sample size? If the answer is f
ullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULL
SCAN?
Thanks
DonnieCan you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie|||Yes, The reindex takes about 4 hours on the table, the update statistics wit
h a fullscan was killed after 11 hours. The size of the table is about 200
million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with[vbcol=seagreen]
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created f
or the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie