we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day.
during the updates, we delete all the previous info ,for that product. So over the course of a day we delete and insert 50% of the data, resulting in new rows that account for 50% of the table
The table is also read from heavily to get this information all throughout the day.
Right now we are seeing issues during heavy inserts. We have tracked this down to index splits. here are some questions:
1) The primary key is an auto number and a bunch of other columns. The auto number is first in order. Does this mean all entries will always be added to the end of index resulting in new leafs and not splits, therefore we do not need to worry about setting a lower fill factor? It's currently at 80%
2) The indexes are in random order, so should we use a lower fill factor such as 50%, it's currently at 80%.
3) is there a better pattern/process we can follow. Such as doing the inserts into another table then bulk copying the results in? The insert processes take hours. This way we alleviate some of the issues. Then the concern becomes the inserting of a lot of rows all at once.
Thanks in advance,