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,

  • Do you use primary key as the clustered index? In a mixed environment with big OLTP workload i would try to split it into 2 tables and use ETL during off hours to update the data. Otherwise you have to find a neat compromise between keeping necessary indexes and growing page splits. – yahor Oct 22 '14 at 19:29
  • yes, we have a primary key as the clustered index. it's an OLTP workload. – ttomsen Oct 22 '14 at 19:55
  • What kind of issues do you see particularly? Is this non-clustered index split causing locking and/or slow queries? If you do not reuse key values during inserts you don't need to adjust fill factor for the clustered index. Increasing fill factor will lead to less data density and more page reads required. – yahor Oct 22 '14 at 20:13
  • Couple of questions - when you say during the updates, we delete all the previous info - are you wiping out all the data or just a subset ? What isolation level you are using ? How are you doing inserts - BULK LOAD, SSIS, SELECT .. INTO ? – Kin Oct 22 '14 at 21:22
  • we delete all previous info PER product. So some records stay. The data is created by inserts from a .NET program which is making multiple remote calls to get the data. – ttomsen Oct 22 '14 at 23:31

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.