October 8, 2008 at 11:09 am
We have a small data warehouse with tables being fully replaced. We load with the clustered index in place and add secondary indexes afterward.
I don't want to waste effort. Should we update statistics on the table and then create the secondary indexes with norecompute off, or create the secondary indexes with norecompute on and then update statistics on the table and indexes as a whole?
October 8, 2008 at 3:20 pm
Unless indexes are heavily fragmented, no need to REORGANIZE/REBUILD the indexes
occasional sp_updatestats won't hurt
but if everything runs smoothly and quickly, don't worry about it
October 9, 2008 at 8:15 am
Well the indexes didn't exist, nor did the table and data, so generating statistics is necessary. sp_updatestats is not a realistic answer either as downstream loaded tables are dependent on other recently loaded tables. So we need to build tables, index, stats, and then reference immediately.
This is a strategy forum. I'm going to be doing this for hundreds of large tables on a regular basis. I worry about every I/O. So, the question is, is there a benefit to generating statistics on the table and indexes as a whole versus generationg statistics on the table and indexes individually? Will one strategy require more passes, will one strategy generate better statistics?
October 9, 2008 at 8:36 am
Dan Ellison (10/8/2008)
We have a small data warehouse with tables being fully replaced. We load with the clustered index in place and add secondary indexes afterward.I don't want to waste effort. Should we update statistics on the table and then create the secondary indexes with norecompute off, or create the secondary indexes with norecompute on and then update statistics on the table and indexes as a whole?
When you create an index the server automatically creates the statistics for that index, so there is no need to create statistics manually after you created the index. There is also no reason to create the statistics before creating the index, because when you create an index the server has to scan the entire table anyway, so you don’t really save any work. Also from my experience most of the time it is better to import the data first and only after you import the data create the clustered index.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply