InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 10:38:00 GMT20http://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxPostponing defragmentation till really necessary as there are some many things involved: usage, fragmentation DB level, fragmentation os level, fragmentation storage level...Mon, 22 Oct 2012 10:47:22 GMTJo Pattynhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspx[quote][b]des.browning (10/2/2012)[/b][hr]'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.[/quote]Catch-22. Not doing it will likely affect the "system's usage", as well.Sun, 21 Oct 2012 18:39:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxDatabases are not self-healing animals, they require maintenance windows, period. I usually do weekly [b]online[/b] 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.:-DTue, 02 Oct 2012 08:55:29 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspx'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.Tue, 02 Oct 2012 03:28:32 GMTdes.browninghttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxAgreed, re-indexing is one of several things that should be automated on your db server from the getgo.:-DMon, 01 Oct 2012 14:51:37 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxI read the article. It would appear that I either didn't emphasise some things to Tony or he chose to ignore them. :-Dp.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.Mon, 01 Oct 2012 07:12:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxYou 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.Mon, 01 Oct 2012 07:02:34 GMTdes.browninghttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxWell, 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)Mon, 01 Oct 2012 06:44:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxstats 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.Mon, 01 Oct 2012 06:39:15 GMTdes.browninghttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspx[quote][b]des.browning (10/1/2012)[/b][hr]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.[/quote]Seriously? You think index and stats maintenance is a waste of time?Mon, 01 Oct 2012 06:35:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxThis 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.Mon, 01 Oct 2012 03:21:38 GMTdes.browninghttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxOne 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).Sat, 29 Sep 2012 01:33:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxI'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.Fri, 28 Sep 2012 20:22:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxThe 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.Fri, 28 Sep 2012 14:36:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366088-263-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Editorial/93873/">Fragmentation Fear</A>[/B]Fri, 28 Sep 2012 13:33:13 GMTTony Davis