April 24, 2007 at 10:33 am
Hi,
Can anyone let me know if DBCC Reidex update stats?
Thanks
April 24, 2007 at 11:24 am
It doesn't do anything to your statistics, you'll have to run UPDATE STATISTICS or CREATE STATISTICS commands.
April 24, 2007 at 11:41 am
The dbcc dbreindex always updates the statistics. You can confirm this yourself by running this simple test in the pubs database, and looking at the statistics date before and after the dbcc dbreindex
use pubs
select [Stats_Updated_Date] = stats_date(object_id('dbo.authors'),1)
dbcc dbreindex ('dbo.authors','',0)
select [Stats_Updated_Date] = stats_date(object_id('dbo.authors'),1)
April 24, 2007 at 11:57 am
Yes the DBCC DBREINDEX updates the statistics automatically. I have tested this.
Thanks Michael for your help
April 25, 2007 at 7:10 am
Yes it will update stats....
April 26, 2007 at 9:24 am
what is difference between sql server 2000 and 2005 architecture
send ans siva.meti@gmail.com plz
April 26, 2007 at 11:53 am
If you reindex do not forget the execute sp_recompile for the table(s) affected and sp_refreshview for the view(s) affected. otherwise you will have up to date statistics and stored procedure plans compiled with out of date statistics potentially causing you performance degredation.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 27, 2007 at 8:40 am
Right now I run Reindex on all tables and then update stats with fullscan? Should I be running sp_recompile and sp_refreshview between those two jobs? This is on sql server 2005.
April 27, 2007 at 9:13 am
Not totally sure on 2K5 but on 2K I can answer. That depends on the method of execution. If you do all table REINDEXES followed by all table UPDATE STATS you need to do this after each step. This is because you have a windows (database size and exeuction time are the variables) when the execution plans are not optimal.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 27, 2007 at 9:36 am
if you are using SQL 2005 better you use ALTER INDEX than DBCC DBREINDEX as it would be removed from future version, more over ALTER INDEX does provide online index rebuild, row & page lock options etc..etc.. check http://msdn2.microsoft.com/en-us/library/ms188388.aspx
Regards
Shrikant Kulkarni
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply