Fragmentation Fear

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Fragmentation Fear

15 posts, Page 1 of 2

1 2 »»

Fragmentation Fear

Rate Topic

Display Mode

Topic Options

Author

Message

Tony Davis

Posted Friday, September 28, 2012 1:33 PM

Mr or Mrs. 500

Group: Administrators
Last Login: Wednesday, May 08, 2013 10:31 AM
Points: 511, Visits: 945

Comments posted to this topic are about the item Fragmentation Fear

Post #1366088

GilaMonster

Posted Friday, September 28, 2012 2:36 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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 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 #1366117

Jeff Moden

Posted Friday, September 28, 2012 8:22 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 32,892, Visits: 26,763

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1366148

GilaMonster

Posted Saturday, September 29, 2012 1:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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 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 #1366168

des.browning

Posted Monday, October 01, 2012 3:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:24 AM
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.

Post #1366413

Jeff Moden

Posted Monday, October 01, 2012 6:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 32,892, Visits: 26,763

des.browning (10/1/2012)

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

--Jeff Moden

Post #1366525

des.browning

Posted Monday, October 01, 2012 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:24 AM
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.

Post #1366528

GilaMonster

Posted Monday, October 01, 2012 6:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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 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 #1366531

des.browning

Posted Monday, October 01, 2012 7:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:24 AM
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.

Post #1366540

GilaMonster

Posted Monday, October 01, 2012 7:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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

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 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 #1366554

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions