Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aurodenver190
Starting Member
2 Posts |
 Posted - 2010-11-16 : 19:49:56
|
| Hi,I have 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 table2. Disable the Nonclustered index using alter index statement.3. Load into Fact table4. Rebuild all nonclustered index by using Alter index statment with Fill Factotr 90Is 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..Thanks,Sam. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-16 : 20:55:06
|
| That process seems pretty ideal. Now way to give you a time thoughthat is typical, you can only determine that through doing your own testing.-Chad |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-11-17 : 04:52:07
|
| Out of curiosity, why use this value...."with Fill Factor 90"...when you also state "These tables are loaded every week with same amount of records, i.e there is no incremental processing. Its a complete load from my base system". Surely there is minimal need for data expansion/alteration as you have described the system. I'm not sure if it would make any material difference to your load/rebuild time...more interested in your logic for same. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-17 : 11:17:23
|
| True. What Andrew said. I missed that. |
|
|
|
|
|
|
Fact table Index rebuild