Fact table Index rebuild RRS feed

  • Question

  • Hi,

    I have a 3 fact tables which hold an average of 40 M records. These tables are loaded every week with same amnt of records, i.e there is no incremental processing. Its a complete load from my base system.

    While i was testing with one of the fact tables , the load wascompleted in 12 mins and inserted around 22M records.( Just a test for my production size load).Then rebuilding indexes took around 8 mins to finsh.We have 16 nonclustered indexes ( 16 foreign keys to dimesnions). This table has  an identity column which is the primary key for the table and that has a clustered index. I am performing the this complete operation as described below.

    1. Truncate the Fact table

    2. Disable the Nonclustered  index using alter index statement.

    3. Load into Fact table

    4. Rebuild all nonclustered index by using Alter index statment with Fill Factotr 90

    Is that time to rebuild those indexes normal ?? I know we have another fact table which holds 120M rows and has 20 Nonclustered index.We follow the same approach as described above for all the fact loads..



    Tuesday, November 16, 2010 10:42 PM

All replies