Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Index Rebuild Taking too Long? Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 2:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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.
Post #1440020
Posted Monday, April 8, 2013 7:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1440102
Posted Monday, April 8, 2013 8:08 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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
Post #1440108
Posted Monday, April 8, 2013 8:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1440116
Posted Monday, April 8, 2013 8:55 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
CLUSTERED INDEX IN_ROW_DATA 2 0 16.66666667 2 3 6 79.9831233 368 0 0 90 123 103.584 NULL
CLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 0.938967136 6 0 0 11 11 11 NULL

Google Doc with Results
Post #1440119
Posted Monday, April 8, 2013 9:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
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_desc alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
CLUSTERED INDEX IN_ROW_DATA 2 0 16.66666667 2 3 6 79.9831233 368 0 0 90 123 103.584 NULL
CLUSTERED INDEX IN_ROW_DATA 2 1 0 1 1 1 0.938967136 6 0 0 11 11 11 NULL

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
Post #1440128
Posted Tuesday, April 9, 2013 7:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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
Post #1440333
Posted Wednesday, April 10, 2013 6:17 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:49 AM
Points: 783, Visits: 731
Is the table being accessed during this maintenance period?
Post #1440761
Posted Wednesday, April 10, 2013 7:15 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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).
Post #1440795
Posted Wednesday, April 10, 2013 7:17 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:49 AM
Points: 783, Visits: 731
These rebuilds need locks, which can be causing your time to climb. Do you have Enterprise installed?
Post #1440796
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse