February 1, 2010 at 3:00 pm
This is for sql2000 table
If a table is created with clustered indexes and non-clustered index, and the table also has non-index statistics (generated from the Query optimizer)
- Question: if you do: dbcc dbreindex(NAME,'',90) to the table, does the indexes gets the statistics updated automatically with 100% statistics?
- Also, how about the non-indexed statistics column (__WA_SYS..), would the statistics for those columns for that table be updated automatically from doing the dbcc dbreindex? If it does, is it also 100% statistics updated?
Need to know so I can determine if running the manual Update statistics on that same table should be done right afterwards?
February 1, 2010 at 3:55 pm
- Question: if you do: dbcc dbreindex(NAME,'',90) to the table, does the indexes gets the statistics updated automatically with 100% statistics?
- Also, how about the non-indexed statistics column (__WA_SYS..), would the statistics for those columns for that table be updated automatically from doing the dbcc dbreindex? If it does, is it also 100% statistics updated?
Statistics are updated with full scan when the index is rebuilt using DBCC DBREINDEX. So the clustered and non-clustered index statistics would be updated with full scan if you are using
DBCC DBREINDEX(table_name, '',90)
I believe the system generated statistics are updated as well but with a sample rate. You can use DBCC SHOW_STATISTICS(table_name, statistics_name) to see when stats were last updated and if it was full scan or a sample rate. If rows and rows sampled are the same then stats were updated with full scan if rows sampled are less than rows, stats were updated using a sample rate.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply