Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

why is my fragmentation so high on non-leaf index levels Expand / Collapse
Author
Message
Posted Friday, January 11, 2008 9:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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?


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #441822
Posted Friday, January 11, 2008 11:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Do you rebuild the index, or reorg it?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #441929
Posted Friday, January 11, 2008 11:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #441933
Posted Friday, January 11, 2008 3:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
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 at GMail
Post #442027
Posted Friday, January 11, 2008 3:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #442029
Posted Friday, January 11, 2008 3:38 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
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.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #442031
Posted Friday, January 11, 2008 5:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #442046
Posted Friday, January 11, 2008 7:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #442066
Posted Monday, January 21, 2008 8:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #445502
Posted Friday, January 25, 2008 2:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 200, Visits: 1,277
Just a question to ask...you're not shrinking the data file after the index rebuild or have autoshrink on?
Post #447359
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse