DBREINDEX
in [Server Configuration]
|
From: Tibor Karaszi on 9 May 2010 13:04 Indeed... I even have this in a blog thread of mine: http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/09/is-statistics-over-non-indexed-columns-updated-by-index-rebuild.aspx -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D73AFB76D7B3Yazorman(a)127.0.0.1... > Tibor Karaszi (tibor_please.no.email_karaszi(a)hotmail.nomail.com) writes: >> No, only the statistics that comes with the index in question. > > But if you rebuild all indexes, it seems that non-index statistics > are updated. But only if you use DBCC DBREINDEX, not ALTER INDEX. > > CREATE TABLE mytable (a bigint NOT NULL, > b sysname NOT NULL) > go > CREATE CLUSTERED INDEX clust_ix ON mytable(a) > CREATE STATISTICS mystats ON mytable(b) > go > SELECT stats_date = stats_date(object_id('mytable'), stats_id), name > FROM sys.stats > WHERE object_id = object_id('mytable') > go > INSERT mytable(a, b) > SELECT object_id, name > FROM sys.objects > go > DBCC DBREINDEX(mytable) -- mystats is updated > --DBCC DBREINDEX(mytable, clust_ix) -- mystats is not updated. > --ALTER INDEX ALL ON mytable REBUILD -- mystats is not updated > go > SELECT stats_date, stats_date(object_id('mytable'), stats_id), name > FROM sys.stats > WHERE object_id = object_id('mytable') > go > drop table mytable > > I ran this SQL 2005 SP3. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |