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

Question

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

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
January 11, 2008 at 9:01 am

#378000

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?

Viewing 15 posts - 1 through 15 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSC-Dedicated Points: 30014 More actions · Answer 2

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 SSC Guru Points: 134017 More actions · Answer 3

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 SSC-Dedicated Points: 30014 More actions · Answer 4

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 SSC Guru Points: 134017 More actions · Answer 5

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 SSC-Dedicated Points: 30014 More actions · Answer 6

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 SSC Guru Points: 1003851 More actions · Answer 7

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.

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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 8

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 Hall of Fame Points: 3490 More actions · Answer 9

Just a question to ask...you're not shrinking the data file after the index rebuild or have autoshrink on?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

can you please post the output from either the index physical stats DMV or dbcc showcontig? Note that showcontig is a blocking operation.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

How many non-leaf pages are there in the index? I think one of the index DMVs will show you that

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 12

I wouldn't be concerned about it - if your indexes are being used regularly then most likely the majority of the upper levels will be in cache anyway. Also, the circumstances where SQL Server does readahead on the level above the leaf level are pretty rare and the only thing that will benefit from removing fragmentation at that level.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 13

Slightly off topic, but if you are planning to re-define your clustered index on one or more non-identity columns, choosing keys found in GROUP BY/ORDER BY clauses is going to give you the greatest performance benefit. Clustered indexes are most beneficial when used to return a ***range*** of records.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

I just now noticed that you specified UPPER levels of the indexes. Do you have the Pad Index option turned on?

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