Some indexes not rebuilt

Question

Some indexes not rebuilt

webrunner

SSC-Dedicated

Points: 31865
More actions
October 24, 2008 at 9:55 am

#194902

Hello,
Does anyone know why some indexes would not rebuild (the Index Physical Stats report says Rebuild and fragmentation is >=75%)?
Our maintenance plans reorganize indexes daily and rebuild them weekly. But some indexes still have the Rebuild recommendation in the report even though the weekly plan has run.
I've even tried scripting out the rebuild code and trying to rebuild indexes for the tables in question, but some still don't rebuild. I'm wondering what can cause this and how to fix it.
Thanks in advance for any advice,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

How large on these tables? How many rows exist and how many pages in the index?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Small tables, especially those that occupy less than one extent (that's 8 pages) generally won't defragment.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Possibly the maintenance jobs are failing part way through?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

webrunner SSC-Dedicated Points: 31865 More actions · Answer 4

Thanks for all the pointers - I'll take a look and report back soon.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Grinja Hall of Fame Points: 3590 More actions · Answer 5

I'm new at the whole index fragmentatation but have found that smaller tables may sometimes show increased fragmentation after rebuilding the index. Particularly lookup tables in my instance.

bang725 Mr or Mrs. 500 Points: 577 More actions · Answer 6

instead of that drop and re-create indexes. schedule this as sql agent jobs