Rebuilding Indexes and Updating Statistics

I believe that when you use DBCCReindex the statistics are updated for each
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for each
> table, but can anyone tell me if it does a fullscan update or a partial scan
> update when using DBCCReindex?|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean the
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
> >
> > I believe that when you use DBCCReindex the statistics are updated for each
> > table, but can anyone tell me if it does a fullscan update or a partial scan
> > update when using DBCCReindex?
>|||ktf,
I have to disagree.
I could not believe your statement, so I tested it, as below.
-- drop table Test
create table Test(id int not null,id2 int not null)
create index CLIX_Test on Test(id)
create nonclustered index NCIX_Test on Test(id2)
insert into Test
select id,(id/2)+((id%2)*1000000000)
from sysobjects
insert into Test values (3,3)
declare @.i int
set @.i=250
while @.i>0
begin
insert into Test
select id+@.i,((id+@.i)/2)+(((id+@.i)%2)*1000000000)
from sysobjects
set @.i=@.i-1
end
go
dbcc show_statistics ("Test",CLIX_Test)
-- nothing
update statistics Test (CLIX_Test) with sample 10 percent
dbcc show_statistics ("Test",CLIX_Test)
-- low number in the "Rows Sampled" column
dbcc dbreindex("Test",CLIX_Test)
dbcc show_statistics ("Test",CLIX_Test)
-- high number in the "Rows Sampled" column
update statistics Test (CLIX_Test) with fullscan
dbcc show_statistics ("Test",CLIX_Test)
-- same number in the "Rows Sampled" column
Now the only thing I could not really explain is some (minor?)
inconsistency in the results after reindexing and after updating the
statistics with fullscan. The Rows Sampled would always be the same, but
sometimes the number of steps in the histogram would differ, causing
different results. I have only seen these differences a few times.
All of the times, both the Rows Sampled and the actual statistics
distribution would be different between the 10% sample and the
reindex/fullscan. There is no doubt about it that reindexing will not
(as a rule) sample just 10 percent of all rows, it is definitely
scanning all rows. I still have no reason to assume that a reindex would
not sample all rows for statistics purposes.
Gert-Jan
ktf wrote:
> Gert-Jan,
> I have been there, done that and I disagree with you.
> Rick,
> If you want to get your performance back after rebuilding indexes you MUST
> run update stats with a FULL SCAN because when you rebuild indexes or run
> update stats statements the selectivity level by default is 10% that mean the
> server scans only 10% and does not touch 90%.
> DO that full scan, have your performance BACK, save some money for your
> company and pray for me.
> v/r
> ktf
> "Gert-Jan Strik" wrote:
> > The entire index is rebuild, so all values are touched and collected. A
> > partial scan would not make sense.
> >
> > Gert-Jan
> >
> >
> > Rick wrote:
> > >
> > > I believe that when you use DBCCReindex the statistics are updated for each
> > > table, but can anyone tell me if it does a fullscan update or a partial scan
> > > update when using DBCCReindex?
> >