Fragmentation Fear

Question

Fragmentation Fear

Tony Davis

SSCarpal Tunnel

Points: 4405
More actions
September 28, 2012 at 1:33 pm

#147312

Comments posted to this topic are about the item Fragmentation Fear

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

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

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

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

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.

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)

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

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

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

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.

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)

des.browning SSC Enthusiast Points: 107 More actions · Answer 6

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.

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

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 SSC Enthusiast Points: 107 More actions · Answer 8

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.

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

I read the article. It would appear that I either didn't emphasise some things to Tony or he chose to ignore them. 😀

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

TravisDBA SSCoach Points: 15780 More actions · Answer 10

Agreed, re-indexing is one of several things that should be automated on your db server from the getgo.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

des.browning SSC Enthusiast Points: 107 More actions · Answer 11

'It's something that should be scheduled and automated.'

True, if you have the window to do it - not always possible without affecting the system's usage.

TravisDBA SSCoach Points: 15780 More actions · Answer 12

Databases are not self-healing animals, they require maintenance windows, period. I usually do weekly online re-indexing between 3-5AM on a Sunday morning, after the Sunday backups have taken place. In that way, I don't affect my U.S. systems adversely, even the 24/7 databases. For overseas databases i adjust accordingly.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

des.browning (10/2/2012)
'It's something that should be scheduled and automated.'
True, if you have the window to do it - not always possible without affecting the system's usage.

Catch-22. Not doing it will likely affect the "system's usage", as well.

--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)

Jo Pattyn SSC-Dedicated Points: 32068 More actions · Answer 14

Postponing defragmentation till really necessary as there are some many things involved: usage, fragmentation DB level, fragmentation os level, fragmentation storage level...