Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fragmentation Fear


Fragmentation Fear

Author
Message
Tony Davis
Tony Davis
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: Administrators
Points: 627 Visits: 1151
Comments posted to this topic are about the item Fragmentation Fear
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
The design of indexes of clustered indexes to prevent fragmentation isn't entirely (or sometimes even mostly) about preventing fragmentation. It's also about preventing page splits, which can be pretty nasty operations in terms of efficiency (many times the work of a single insert) and logging.

The resulting low average page density is also more of a concern than the fragmentation itself in many cases. Sure, a page that's half empty is only wasting 4kb of memory, but what if they entire buffer pool (say 64GB of it) is on average 60% full. That's a lot of wasted memory.

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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51705 Visits: 40308
I'll add to what Gail so correctly stated by saying the index defragmentation isn't as easy as you say, Tony. If you need to rebuild an index, it sometimes prevents access to the underlying tables unless you rebuild the index in an ONLINE fashion. That's not always possible either because you don't have the Enterprise Edition of SQL Server or you have blobs (VARCHAR(MAX), etc) in the table that prevent it.

I agree that most front end code can withstand litterally years of not doing any index maintenance and still manage to get index seeks when looking up the paltry one or two rows at a time even from multiple tables. But then there's reporting, building datamarts, and other batch processes that all support what the front end can do. You also have to remember that every nonclustered index also contains the clustered index (unless the table is a HEAP) and all of that will very much affect the much desired seek/range scan need for truly effective batch processing.

I've also seen it where fragmentation in the form of extent-splits on nonclustered indexes during inserts from the front end have caused timeouts so bad that it rendered the front end code virtually useless.

I'd say that there's probably not enough fear about fragmentation in any of its forms.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
One other point... Fragmentation will not affect whether a query plan uses a seek or a scan, because the optimiser does not take fragmentation into account.
Low page density however might (needs testing).

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


des.browning
des.browning
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
This debate ran in Oracle a few years ago and the same conclusions were reached as seems to be the case in Sql Server - except in a very few clearly defined cases they are a waste of time and can actually make things worse. Great generator of overtime,though.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51705 Visits: 40308
des.browning (10/1/2012)
This debate ran in Oracle a few years ago and the same conclusions were reached as seems to be the case in Sql Server - except in a very few clearly defined cases they are a waste of time and can actually make things worse. Great generator of overtime,though.


Seriously? You think index and stats maintenance is a waste of time?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
des.browning
des.browning
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
stats maintenance - no. What I actually said was that the practice of regularly rebuilding Oracle indexes as they were 'unbalanced' or 'untidy' has fallen into disuse, and that it appears that the same realisation is happening to Sql Server users.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
Well, if that 'realisation' happens to SQL Server DBAs there will be more optimisation work than ever for the SQL Server consultants.

Rebuilding indexes in SQL Server is not a waste of time. SQL Server != Oracle. Very different index architectures. Very different 'best' practices.

Sure, the fragmentation itself may not be an issue ('may not be', not 'is not'), but the low page density that results from page splits or lots of deletes can be very nasty. Nothing like a table with 20000 rows taking up over 5000 pages when it could fit in less than 2000. Hell, I've seen a table with about 2000 rows take up just under 2000 pages because of the pattern of deletes and because the indexes were never rebuilt. That's a waste of space (in memory, on disk, in backups) and of time (backing up, reading off disk, consistency checks, etc)

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


des.browning
des.browning
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
You may well be right and it could be entirely necessary. I was commenting on the particular article that prompted this thread, which suggested that it wasn't necessary, and observed that this was paralleling what happened in Oracle. Of course the source article was referring to index rebuilding to reduce fragmentation, which was the aspect I was commenting on.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54336 Visits: 44637
I read the article. It would appear that I either didn't emphasise some things to Tony or he chose to ignore them. :-D

p.s. If any DBA ever told me they wanted overtime so they could do index maintenance, I'd laugh. It's something that should be scheduled and automated.

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


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