All indexes in one small table are taking too long to rebuild

Question

All indexes in one small table are taking too long to rebuild

Gamleur84

Default port

Points: 1475
More actions
February 3, 2017 at 10:20 am

#319089

Hello all !
I am using https://ola.hallengren.com database maintenance script. I am running the IndexOptimize every night.
I found a REBUILD Index query stuck. I killed the process and ran the query manually, same results: The ALTER INDEX query never ends.
Table
Index Space: 21.297 MB
Row Count: 24802
Data space: 6.203 MB
Index_1
Type: Nonclustered
Page fullness: 28.91%
Total fragmentation: 99.44%
Average row size: 99
Depth: 3
Leaf-level row: 24802
Maximum row size: 124
Minimum row size: 50
Pages: 1065
As you can see, the index is caught by the script because it is just over 1000 pages and has very fragmentation. In my opinion, it is not normal the REBUILD takes 5+ hours on such a small index. I have 5 indexes on that table, 3 of them have less than 1000 pages. The other one with more than 1000 pages has the same problem. I have a cluster index on the table.
any ideas?
Thank you

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

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

Eric M Russell SSC Guru Points: 125255 More actions · Answer 1

The index process may not be slow but rather stalled in some type of blocked state. Use SP_WHO2 while it's running to confirm.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gamleur84 Default port Points: 1475 More actions · Answer 2

yes, I think you are right. Will do some homework and come back with results

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Use sp_whoisactive to see both blocking issues as well as work actually done by a spid. You can also run it in differential mode and see work deltas.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Chris Harshman SSC-Forever Points: 42179 More actions · Answer 4

Gamleur84 - Friday, February 3, 2017 10:20 AM
I am using https://ola.hallengren.com database maintenance script. I am running the IndexOptimize every night...

How often does this particular index get rebuilt? Running index optimization every night seems excessive, but it also seems odd that this index would have such low fullness and high fragmentation. What is the key column(s) and the fill factor on this index? I'd think with an index so small that it would rebuild in less than a second if it could get past whatever blocking issues are going on.

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

Heh... stop rebuilding indexes. It's not worth it and only causes blocking the next day. I haven't rebuilt indexes on my production box for over a year (last date was 2016/01/17) and performance only got better as the "natural Fill Factor" took over. Instead, focus on ensuring that stats are updated more often.

--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