indexing architechture

OK:

1. after rebuilding indexes, shouldn't sp_updatestats and DBCC UPDATEUSAGE be run for best performance?

2. What exactly are sp_updatestats and update usage doing? It looks like (from BOL) that updating usage would be updating the IAM and the page free space, and updating stats would just update the index/row pointers. Indexes are rebuilt nightly where I am currently working, however, unallocated space is consistently negative.

3. rebuilding or defragging the indexes should defrag the tables, right? As in, re-allocate free space depending on fillfactor...

4. for a reporting database, shouldn't the fillfactor be low? That way, you would have fewer page splits during loading, and as far as querying, by the time you are done with your loading, the engine should have evened out the allocation...

Rebuilding indexes (drop/create or DBCC DBREINDEX) will automatically update statistics.

If you are rebuilding indexes each night, then you probably don't need to worry about the statistics, unless you import bulk data throughout the day, truncate tables or significantly change the data distribution through large amounts of updates before the next index rebuild.

DBCC UPDATEUSAGE simply corrects inaccuracies in the sysindexes table.

sp_updatestats runs UPDATE STATISTICS on all user tables in the database.

If you rebuild a clustered index, it will effectively rebuild the table. There is an optional parameter in the reindex command to specify a new fill factor. If not specified, the original fill factor will be used for the reindex.

DEFRAG defragments the leaf level of an index using the original fill factor.

In summary, if you have the luxury of doing an index rebuild each night, then you're in a good position, and shouldn't really need to worry about defrag or statistics.

|||

THanks...however,

I don't quite understand what UPDATEUSAGE does...we have negative unallocated space on a continual basis. Our database is 156GB, and it shows up with 66GB as negative unallocated. THe only thing that fixes this is UPDATEUSEAGE -

1 - the query analyzer can't find the right query plan with -unallocated, right? I'm thinking that it doesn't have the correct IAM, but I don't know...

2- we do loads every night. Sometimes millions of rows. Unfortunately, the DBAs do the re-indexing BEFORE the load, with default fillfactor of 90. :(

(this was just a whine)

3- can someone tell me exactly what updating stats does that is different from updating useage? Inquiring minds want to know...

4- there are no updates during the day, it's read only for reporting. So, It seems to me that we should set the fillfactor low before the load - however, is it going to negatively impact the reporting? Doesn't SQL server start picking pages and extents with an algorithm that evenly distributes the data on imports and inserts?

5 - In what order should the above items be run?