Rebuild Index Hangs

Question

Rebuild Index Hangs

jsb

SSC Eights!

Points: 888
More actions
July 6, 2011 at 7:47 am

#243499

I am having problems with a rebuild index statement that our maintenance script generates. When it's run in our development environment where the MT102 table has 36,000 rows the statement just hangs. It's uses no cpu and the i/o never changes but the spid running the statement places a lock on the table. The rebuild index statement runs fine in production when the table has 1200 rows. This is a vended application so I can't drop the index but I would like to know if anybody else has ever had this problem and/or why this is an issue.
--Tsql
ALTER INDEX [GENXMLIDX5102] ON [dbo].[MT102] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, MAXDOP = 2);
It also will run in development with the MAXDOP set to 1. Here is the table definition
-- Table definition
CREATE TABLE [dbo].[MT302](
[MV_PK] [int] NOT NULL,
[Mv_USAGE_ID] [int] NULL,
[LAST_MODIFIED_TIME] [datetime] NULL,
[EFFECTIVE_DATE] [int] NULL,
PRIMARY KEY CLUSTERED
(
[MV_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [xPression_CR_Dvlp]
/****** Object: Index [GENXMLIDX5302] Script Date: 07/06/2011 08:29:25 ******/
CREATE NONCLUSTERED INDEX [GENXMLIDX5302] ON [dbo].[MT302]
(
[Mv_USAGE_ID] DESC,
[MV_PK] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [xPression_CR_Dvlp]
/****** Object: Index [index_MT302] Script Date: 07/06/2011 08:29:25 ******/
CREATE NONCLUSTERED INDEX [index_MT302] ON [dbo].[MT302]
(
[Mv_USAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Trigger [dbo].[MTrig26] Script Date: 07/06/2011 08:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[MTrig26] ON [dbo].[MT302] AFTER UPDATE AS UPDATE MT302 SET LAST_MODIFIED_TIME = getdate() FROM inserted WHERE MT302.MV_PK = inserted.MV_PK
GO
ALTER TABLE [dbo].[MT302] ADD DEFAULT (getdate()) FOR [LAST_MODIFIED_TIME]
GO

Viewing 6 posts - 1 through 5 (of 5 total)

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

jasonwi1202 SSC Veteran Points: 291 More actions · Answer 1

Isn't the MAXDOP option reliant on the number of processors that the server has? Does your test server have only 1 processor?

Jason

Webmaster at SQL Optimizations School

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

Can you updates stats on the table and try rebuilding the indexes? Just a thought...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

jasonwi1202 (7/6/2011)
Isn't the MAXDOP option reliant on the number of processors that the server has? Does your test server have only 1 processor?

Yes and no. MAXDOP will not magically allow you to use more processors than you have but you can always specify fewer processors. The reasons why you may want to do so is 1) acts a bit as a poor-man's resource governor and 2) as you'll read in Grant Fritchy's fine books on execution plans, sometimes defeating parallelism with MAXDOP 1 can actually increase performance depending, of course, on what the code is doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jsb SSC Eights! Points: 888 More actions · Answer 4

Here is how the MAXDOP is calculated.

Select @CPUS = cpu_count / hyperthread_ratio

from sys.dm_os_sys_info;

SET @RebuildParams = 'SORT_IN_TEMPDB = ' + @TEMPDB + ', ONLINE = ' + @Online + ', MAXDOP = ' + CAST(@CPUS as varchar) -- + ', ALLOW_PAGE_LOCKS=' + @ALLOW_PAGE_LOCKS

It's setting the MAXDOP to be the number of cpu's on the server.

jsb SSC Eights! Points: 888 More actions · Answer 5

I tried updating statistics on the table and that had no effect however changing the MAXDOP to 1 allowed the rebuild statement to complete so I have a work around but I still unclear as to why Sql Server should have any problems rebuilding an index on a table with 36,000 rows :unsure: