DBREINDEX
in [Server Configuration]
|
From: RG on 9 May 2010 08:10 In addition to index statistics, does dbreindex update column statistics? Thanks in advance
From: Tibor Karaszi on 9 May 2010 08:36 No, only the statistics that comes with the index in question. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "RG" <nobody(a)nowhere.com> wrote in message news:21C2D0B1-A4D6-4E41-80A6-63E85F92EDA3(a)microsoft.com... > In addition to index statistics, does dbreindex update column statistics? > > Thanks in advance
From: Erland Sommarskog on 9 May 2010 08:37 RG (nobody(a)nowhere.com) writes: > In addition to index statistics, does dbreindex update column statistics? This script will tell you: 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(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) go SELECT stats_date(object_id('mytable'), stats_id), name FROM sys.stats WHERE object_id = object_id('mytable') go drop table mytable -- 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
From: Uri Dimant on 9 May 2010 08:47 Yes, see if the below test helps you CREATE TABLE test ( c INT IDENTITY(1,1) PRIMARY KEY,c1 CHAR(10)) INSERT INTO test (c1) SELECT REPLICATE('a',10) GO 100 SELECT * FROM sys.stats WHERE OBJECT_ID=OBJECT_ID('dbo.test') SELECT * FROM test WHERE c1='a' SELECT * FROM sys.stats WHERE OBJECT_ID=OBJECT_ID('dbo.test') DBCC SHOW_STATISTICS (test,_WA_Sys_00000002_2863CE43) DBCC DBREINDEX (test, '', 100) DBCC SHOW_STATISTICS (test,_WA_Sys_00000002_2863CE43) "RG" <nobody(a)nowhere.com> wrote in message news:21C2D0B1-A4D6-4E41-80A6-63E85F92EDA3(a)microsoft.com... > In addition to index statistics, does dbreindex update column statistics? > > Thanks in advance
From: Erland Sommarskog on 9 May 2010 11:16
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 |