Post reply

Replacing clustered index on Very Large Table -- transaction log implications

  • April 8, 2010 at 12:39 pm

    #156852

    Is there a way to predict what impact dropping then recreating the clustered index on a 88m row table will be?

    The table is currently:

    CREATE TABLE [dbo].[IRI](

    [Geography] [varchar](100) NULL,

    [Product] [varchar](100) NULL,

    [AsOf] [int] NULL,

    [UPC_1] [bigint] NULL,

    [WtdDist_1] [decimal](16, 8) NULL,

    [DSales_1] [decimal](16, 8) NULL,

    [CSales_1] [decimal](16, 8) NULL,

    [USales_1] [decimal](16, 8) NULL,

    [ID] [bigint] IDENTITY(1,1) NOT NULL,

    [GEOID] [int] NULL,

    [PRODUCTID] [int] NULL,

    CONSTRAINT [PK_IRI_ID] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    I would like to cluster it on UPC_1,AsOf,GEOID.

    There are 13784 UPC_1 values, 104 AsOf values, and 74 Geoid values.

    I have a maximum log file size of 7 gigs. The db is currently in Bulk Logged mode. I would like to avoid dropping the current clustered index, then find myself unable to create the new one. Any advice is appreciated. D. Lewis

  • April 9, 2010 at 8:33 am

    #1149161

    I guess the table is about 25GB so I think the log will fill and then the statement will stall.

    How about:

    - Create a new copy of the table (with the new clustered index defined).

    - Backup the DB, make sure there are no users on it.

    - Set the DB to Simple.

    - Copy all the data into the new table in chunks, doing a CHECKPOINT after each chunk (you can do a loop, with a range of ID values specified on each pass round the loop).

    - Drop the original table, rename the new one to match the old name.

    - Set the DB back to Bulk Logged.

    - Take a new backup.

  • April 9, 2010 at 1:05 pm

    #1149331

    For an ideal situation, columns making a clustered index should be not nullable and data of a column should be as unique as possible. The columns for the proposed clustered index are not well fit these criteria and the number of records of the columns unlikely represents the 88 million record table. You may also want to look into other available index options.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply