Home Forums SQL Server 2008 SQL Server 2008 - General any way to reindex gigantic table efficiently? RE: any way to reindex gigantic table efficiently?

  • July 21, 2014 at 12:59 am

    #1730898

    cmoy (7/20/2014)

    so I have a time series table consisting of 3 columns: a key, a timestamp and a value. The primary key is of course on the key and timestamp. The table is about 80 billion rows. Certain data is deleted from the table every month. This has caused massive fragmentation on the table. Partitioning the table based on say a time doesn't do much good because many of the keys need to remain for all time periods and the key is nothing more than a generated identity.

    I do have Enterprise Edition (SQL Server 2008 R2) so I can do an online index rebuild, but if I'm not mistaken, an online rebuild is making a full copy of the primary key. The table itself is about 1.6 terabytes and I only have about 200Gb worth of disk space that is free. I can't span to other drives for now. Anyone have any suggestions on how I can reindex this table?

    The table is used 24x7 - pretty much no maintenance time is available. However, I am possibly going to switch to a new server on new storage, so perhaps there's a way to redo the table on the new server before switching. However, this is not going to be easy as 250 million rows are written to this table on a daily basis.

    You cannot rebuild that table with such free space you have. You can add some disk space and add some files of/to the tempdb, so then you use the tempdb to sort/rebuild the index online.

    Usually tables with over 100 million rows are about to be partitioned.

    Because you delete data every month, you should have partitioned your table. With just one switch you're deleting a sub-table (with truncating the stage table) and don't introduce fragmentation to the main table.

    Maintenance of partitioned table takes less time. Apart that you can introduce a special maintenance to that table to rebuild only several partitions, and not all, which is an advantage.

    You can decrease the FillFactor to 99 or 98 to avoid fast fragmentation.

    Why is your key composite of Id-identity and timestamp? you know the advantages of key with identity property.

    Igor Micev,My blog: www.igormicev.com