Index Rebuild Taking too Long?

  • I have an instance running the following version:

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    I have a job that runs on the instance that rebuilds indexes that meet a certain threshold, but for some reason I have a small table's clustered index that takes a really long time to rebuild.

    What are some of the factors that can affect this?

    As an example of how much of an outlier this timing is:

    The index shows 368 pages using sys.dm_db_index_physical_stats and it took 632 minutes to run; while another table that shows 62,634,900 pages only took 5 minutes.

    To confirm the DMV I ran a count on the table and it only contains 368 rows...the index rebuild does not change the fragmentation so upon reporting completion it ends with the same amount of fragmentation. I do find it odd that it is using a page per row...there are no large data type columns in the table.

  • There is little point to rebuilding an index with only 368 pages. The general consensus is not to bother with indexes that have under 1,000 pages.

    As for why an index with 368 pages takes over 10 hours to run...that is a bad sign. Just to confirm, are you sure the logs are being written correctly and that the 632 minutes are being attributed to the correct rebuild operation?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Good reply! You are right I should put in a minimum page count. Still annoying that it is taking so long. I would of said the same thing about the logging method, but it is always the same table the keeps taking so long.

    If I can test that it truly is taking so long, should I have my storage team check out the disk? I run CHECKDB regularly and it doesn't turn anything up.

    Thank again for the reply!

    -Dane

  • Please post the table and clustered index definition and the entire row for the index from the physical stats DMV run with DETAILED analysis.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Here is the build script:

    CREATE TABLE [dbo].[item](

    [item_urn] [int] NOT NULL,

    [cat_urn] [int] NOT NULL,

    [item_mnc] [varchar](10) NOT NULL,

    [item_descr] [varchar](50) NULL,

    [item_active] [char](1) NULL,

    [item_device] [varchar](20) NULL,

    [item_type] [char](1) NULL,

    [dept_urn] [int] NULL,

    [modified_by] [varchar](16) NULL,

    [mod_timestamp] [datetime] NULL,

    [mnc_descr_mod] [char](1) NULL,

    [man_urn] [int] NULL,

    [emp_urn] [int] NULL,

    [link_type] [char](1) NULL,

    [link_urn] [int] NULL,

    [mod_timestamp_server] [datetime] NULL

    ) ON [PRIMARY]

    SET ANSI_PADDING OFF

    ALTER TABLE [dbo].[item] ADD [inspd_loc] [char](1) NULL

    ALTER TABLE [dbo].[item] ADD CONSTRAINT [PK_item] PRIMARY KEY CLUSTERED

    (

    [item_urn] ASC

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

    This is a vendor's table so I don't have control of the definition...

    Here are the results from the DMV:

    index_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count

    CLUSTERED INDEXIN_ROW_DATA2016.6666666723679.98312333680090123103.584NULL

    CLUSTERED INDEXIN_ROW_DATA2101110.938967136600111111NULL

    Google Doc with Results

  • dkschill (4/8/2013)


    Here is the build script:

    CREATE TABLE [dbo].[item](

    [item_urn] [int] NOT NULL,

    [cat_urn] [int] NOT NULL,

    [item_mnc] [varchar](10) NOT NULL,

    [item_descr] [varchar](50) NULL,

    [item_active] [char](1) NULL,

    [item_device] [varchar](20) NULL,

    [item_type] [char](1) NULL,

    [dept_urn] [int] NULL,

    [modified_by] [varchar](16) NULL,

    [mod_timestamp] [datetime] NULL,

    [mnc_descr_mod] [char](1) NULL,

    [man_urn] [int] NULL,

    [emp_urn] [int] NULL,

    [link_type] [char](1) NULL,

    [link_urn] [int] NULL,

    [mod_timestamp_server] [datetime] NULL

    ) ON [PRIMARY]

    SET ANSI_PADDING OFF

    ALTER TABLE [dbo].[item] ADD [inspd_loc] [char](1) NULL

    ALTER TABLE [dbo].[item] ADD CONSTRAINT [PK_item] PRIMARY KEY CLUSTERED

    (

    [item_urn] ASC

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

    This is a vendor's table so I don't have control of the definition...

    Here are the results from the DMV:

    index_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count

    CLUSTERED INDEXIN_ROW_DATA2016.6666666723679.98312333680090123103.584NULL

    CLUSTERED INDEXIN_ROW_DATA2101110.938967136600111111NULL

    Google Doc with Results

    The leaf-level does not have 368 pages, it only has 6 pages. Unless there is a bug out there I am not seeing then I cannot explain a 10 hour rebuild for this index.

    The only thing that looks odd is that the table was created with ANSI_PADDING ON, and then a new column was added at some point and ANSI_PADDING was OFF. That's usually a deployment mistake and is not intentional but does not typically cause trouble. I have a database (from a third-party vendor coincidentally) that has this all over the place and index rebuilds run fine. It's also 2005 but is on SP4.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I will see if I can get a backup restored to another box, and see if the same issue shows up...and if it does then I will make adjustments to the restored db to see if I can get at root cause.

    If I find anything I will be sure to post it here.

    Thanks for the help!

    -Dane

  • Is the table being accessed during this maintenance period?

  • Probably...but no more than the other tables. I do notice that a lot of blocking occurs when it is being rebuilt (even with online=on).

  • These rebuilds need locks, which can be causing your time to climb. Do you have Enterprise installed?

  • ONLINE=ON is an Enterprise feature.

    The rebuild operation, even with ONLINE=ON, can be blocked depending on the iso level and what is using that table...but 10 hours would be a seriously long running transaction and it would be surprising that if something like that were going on that it was not impacting anything else in the system besides this one rebuild attempt.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Yes. We are using Enterprise. It is just odd that it leads to a lot of blocking though...as soon as the script starts up I see processes marked as Head Blockers in the master database.

  • dkschill (4/9/2013)


    I will see if I can get a backup restored to another box, and see if the same issue shows up...and if it does then I will make adjustments to the restored db to see if I can get at root cause.

    If I find anything I will be sure to post it here.

    Thanks for the help!

    -Dane

    Incase if you haven't tried this :

    i ) Check if there is any blocking while you are rebuilding.

    ii) Run read/Write per sec perfmon counter against the drive on which the database is on while you are rebuilding the index.

    Let us know how it goes.

  • Just ran a reorg and it finished in less than second...tried the rebuild and it immediately created a backup on the master database. I will have to wait until Saturday night before I can run the rebuild for any length of time (and I will be sure to gather the metrics then).

    Thanks for all the help and I will be sure to update this topic.

    Thanks for the help,

    Dane

  • dkschill (4/10/2013)


    Just ran a reorg and it finished in less than second...tried the rebuild and it immediately created a backup on the master database. I will have to wait until Saturday night before I can run the rebuild for any length of time (and I will be sure to gather the metrics then).

    Thanks for all the help and I will be sure to update this topic.

    Thanks for the help,

    Dane

    I am not following this, what do you mean by ".tried the rebuild and it immediately created a backup on the master database". What does backup of db has anything to do with rebuild?

Viewing 15 posts - 1 through 15 (of 34 total)

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