none
Indexing Help

    Question

  • Hello,

    I have table whose current size is around 1 TB.Everyday We are performing DML operation on table with millions of records.Now I have couple of indexes created on that table.To make index get updated would it be possible that We can just rebuild index  on today's DML affected records instead of on whole table all records.
    Tuesday, September 02, 2014 6:54 AM

Answers

  • How should a filtered index help for your requirement? And you can't reindex only "todays affected records". Only if you have a partition defined on a "last update stamp" in table, then you could reindex that one partition on that one index, but not on the other indexes.

    You could reorganize the index(es) instead,


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 02, 2014 3:43 PM
  • Table Partitioning feature will help here to segregate data into different partitions (which can reside in different file groups). Another option is to have an archive DB to store historical data. Table partitioning will require least maintenance. Having archive DB will entail some maintenance.
    Table partitioning is a way to efficiently manage large tables. This feature lets you partition a table based on a certain datetime field (like transaction time) and this will help to switch out large amounts of data in a minute or less (you can switch a year's worth of data out to another table and/or delete it in less than a minute as it is a metadata operation).

    Table partitioning feature will help even if you go with creating separate tables for each year - you can switch out the data faster from the main table without blocking production activities.  If you wish to reduce the size of the current DB, you can still use this feature to switch the yearly data onto to a new table within the same DB and then have a job move the new table to the archive DB without affecting production activities

    In addition, you will see a performance improvement for all queries that are filtered on this partitioned column (more so if the partitions are stored on different volumes)


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 02, 2014 7:19 PM

All replies

  • I have gone through several forums I think Filter Index is somewhat matched with my requirement.Can anyone confirmed?
    Tuesday, September 02, 2014 7:13 AM
  • How should a filtered index help for your requirement? And you can't reindex only "todays affected records". Only if you have a partition defined on a "last update stamp" in table, then you could reindex that one partition on that one index, but not on the other indexes.

    You could reorganize the index(es) instead,


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 02, 2014 3:43 PM
  • I am not sure what you are trying to achieve.

    Every index automatically gets updated as part of any (relevant) data change.

    Over time, indexes may get fragmented if there are many (relevant) data changes and cause performance degradation. Reindexing or defragmenting these fragmented indexes can restore the performance.

    What is the problem you are trying to solve?


    Gert-Jan

    Tuesday, September 02, 2014 7:04 PM
  • Table Partitioning feature will help here to segregate data into different partitions (which can reside in different file groups). Another option is to have an archive DB to store historical data. Table partitioning will require least maintenance. Having archive DB will entail some maintenance.
    Table partitioning is a way to efficiently manage large tables. This feature lets you partition a table based on a certain datetime field (like transaction time) and this will help to switch out large amounts of data in a minute or less (you can switch a year's worth of data out to another table and/or delete it in less than a minute as it is a metadata operation).

    Table partitioning feature will help even if you go with creating separate tables for each year - you can switch out the data faster from the main table without blocking production activities.  If you wish to reduce the size of the current DB, you can still use this feature to switch the yearly data onto to a new table within the same DB and then have a job move the new table to the archive DB without affecting production activities

    In addition, you will see a performance improvement for all queries that are filtered on this partitioned column (more so if the partitions are stored on different volumes)


    Satish Kartan http://www.sqlfood.com/

    Tuesday, September 02, 2014 7:19 PM