SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


why is my fragmentation so high on non-leaf index levels


why is my fragmentation so high on non-leaf index levels

Author
Message
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 4673
I run sys.dm_db_index_physical_stats daily on almost every server and db in our environment and have so for months. i've always noticed that anything higher than index_level 0 my fragmentation levels are pretty high.

i haven't seen any performance problems, but could never find any reason why this is so even the day after that index is rebuilt. does anyone have any idea?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87137 Visits: 45267
Do you rebuild the index, or reorg it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 4673
we only do rebuilds since it saves us from doing an update statistics

we do alter index rebuild with (online = on) and once in a while drop and recreate it with create index
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
how much free space is there in the database and what is the growth increment for the data file(s)?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 4673
there is around 48GB free space in the db and file growth is set at 100MB. there is only one datafile because this is a replicated copy.

i think there was a bug in the alter index script i have because of some changes i made a while back. i'll see how it runs in the next few days
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12389 Visits: 8548
48GB sounds like a lot - unless you only have a few massive tables in the database. Don't forget too that sp_spaceused (if that is what you used to determine free space) doesn't maintain accurate numbers. The usual culprit for failure-to-defrag is not having sufficient free space (usually due to poor size/growth increment control) in the database. Hopefully you DO have a bug and it simply hasn't been doing anything. Hehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 4673
the database completely changes in data every 2-3 months as new billing data is added and old data is archived to a different server. one billing cycle is larger than the rest so this probably explains the free space.

and one table is pretty much the entire db with the other 10 or so tables tiny in comparison
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85867 Visits: 41091
You should probably check which columns are in the index vs what you update in the tables... if you're modifying those columns, that'll certainly cause the index to jump about. Also, you should probably ensure that you have an effective clustered index that prevents data from being inserted in the middle of the table on large tables.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 4673
i did a drop/recreate of the index and same thing. checked a few others and even after the days the index is rebuilt the level 1 of the index is still 99% fragmented.

our clustered index is an identity column and we have plans to change it to another column for faster selects on some queries so this may solve it.
humbleDBA
humbleDBA
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 1508
Just a question to ask...you're not shrinking the data file after the index rebuild or have autoshrink on?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search