Rebuilding an index and update statistics

Hi all,
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.
If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
>
|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...[vbcol=seagreen]
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:
|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.

> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP
|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
sql