SQLServerCentral / Discuss Content Posted by Christoffer Hedgate / Article Discussions / Article Discussions by Author / Fragmentation 101 / Latest Posts

RE: Fragmentation 101

matthew.peters — Mon, 06 Dec 2010 23:11:51 GMT

Hey GuysIs there anyway to get these results returned by DBCC SHOWCONTIG through a system view. I've been using sys.dm_db_index_physical_stats but this doesn't give the extent fragmentation.Reason being is that I want to run this across multiple server through a .net program which I've developed but it takes so long.

RE: Fragmentation 101

Dude76 — Thu, 22 Oct 2009 05:05:41 GMT

Hi.3 years after its last publication, this article still sounds great :)I think that it dismissed a T-SQL command that is a good alternative of running 'DBCC DBREINDEX (tablename)' :ALTER INDEX ALL ON [tablename] REBUILD WITH ( PAD_INDEX = ON, FILLFACTOR = yourfillfactor )Why ?dbreindex offer the fillfactor paramter only if you specify an index; if not, or if fillfactor is not provided, it will use (and fill) 100%.So any new insert will make a split of page (think about uniqueidentifier datatype, which will not be 'sequential' ;) ).Alter index offer pad_index and fillfactor (default is 0, value of freespace this time).An other great reason is that you'll be able to rebuild the indexes for a partition, so only where it must be.what do you think about that ?

RE: Fragmentation 101

toolmakersteve1 — Wed, 15 Oct 2008 18:47:19 GMT

[quote]See BOL:CREATE INDEX, DROP_EXISTING"Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account"I learnt this the hard way... [/quote]Umm, whatever you learned the hard way, I doubt it was due to DROP_EXISTING. What BOL is saying is that it rebuilds NCIs if need be, otherwise doesn't waste the time. However I see one caveat "If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index." Not something I'm ever likely to do, but figured I'd mention it, since I was about to claim that DROP_EXISTING never gets you in trouble. Anyway, unless you're doing something advanced, in a complex setup, it won't.

RE: Fragmentation 101

Paul G-468777 — Fri, 18 Jan 2008 12:09:40 GMT

I did find this article very informative and, I believe, explained a seemingly complex subject quite well. But then again, I am not by training a DBA - I am just learing to dance as fast as I can.

RE: Fragmentation 101

Paul G-468777 — Fri, 18 Jan 2008 12:06:01 GMT

I am having some "drinking from the firehose" moments where situation dictates I have to learn some things quickly and have limited tolerance for error. Hopefully, I can state my question succinctly and someone will be willing to offer advice.I ran the DBCC scan on the DB and with some of the larger tables (500,000+ records) it was reporting over 70,000 pages with extent fragmentation in excess of 30% and logical fragmentation in excess of 40%. My understanding is this is a DB badly in need of defrag. (Side note, my understanding is that an earlier rev siilarly used would "in explicably become inaccessible and it looked like it was the tempdb" was the explanation)Would your recommendation be to:1) defrag with something like DK and then use REINDEX2) RECREATE the indexes3) something else? (to my limited understanding DEFRAG would not be a best candidate in this case)Hopefully I have offered enough information to hazard an opinion.

RE: Fragmentation 101

Larry Leonard-399461 — Sat, 29 Sep 2007 23:12:09 GMT

There are at least five or six factual errors in this article, which I was going to list but there got to be too many. What's the definition of "internal fragmentation" again?:pinch:

RE: Fragmentation 101

Greg Hartlaub — Mon, 27 Feb 2006 13:16:00 GMT

Hi,I'm having negative experience with Diskeeper. It's locking us out of SQL when it's defragging the server. Defrag is scheduled weekly on Saturdays when we usually aren't hitting the db.I'm running Win2003 Server and SQL 2000 on Raid 5 array. Two db's with the problem are 66 and 50 Gb each. Hard drive is 410 GB with 50% free. First noticed it 3 weeks ago when a db maintenance job blew up. Changed it to complete before the defrag began and this worked. However, a few data entry folks were in this Saturday am and could no longer access the db as soon Diskeeper started. Totally locked out. And PO'd.From the thread it sounds like this shouldn't happen with Diskeeper. Is there an option/setting that we're missing? Other than scheduling the defrag for later on Saturday, looking for a better solution.Any ideas? Our network admin told me SQL has a defrag function. Since I already regularly defrag indexes weekly I'm not sure what he's talking about.Thanks

RE: Fragmentation 101

Crispin Proctor — Fri, 10 Feb 2006 08:52:00 GMT

Regarding

"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."

Diskeeper monitors the IO queue and throttles back when this rises so the hit to the machine is near nothing.

You could, essentially, run the defragger every hour. Why though...

RE: Fragmentation 101

icata — Fri, 10 Feb 2006 08:45:00 GMT

For file defragmentation, I heard indeed that Diskeep does a good job been able to defrag files in use and base on a schedule, never use it though.

Regarding

"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."

I use it on one of our production servers and during the process it made the tempdb inaccessible. Even if I try to stop the process the SQL Server was inaccessible. I had to restart it.

RE: Fragmentation 101

Alex-217289 — Tue, 10 Jan 2006 12:30:00 GMT

Realizing that fragmentation is an inevitability, we allocate time each night for a complete run of DBCC DBREINDEX on each database. Of course, as servers fill and client requests increase, the maintenance windows shrink (what a shame) so continuing this practice may become rather difficult, forcing a switch to a 1x a week schedule or something.There is a gold lining though. As a couple others have mentioned, it is possible to do a complete defrag (logical and physical) whole the databases are online. Most "modern" tools such as D.K. allow this. We've recently upgraded to tthe latest version of this tool and find it not only very effective at defragging tasks but much more efficient than expected. As always, I would suggest plenty of development testing befiore using on your production servers but to date, we've had no issues in any of our environments. There is a 30 day free trial. Use it!

RE: Fragmentation 101

David.Poole — Mon, 09 Jan 2006 07:03:00 GMT

I have read an article on physical file defragmentation on a SAN or RAID system www.raxco.com/products/perfectdisk2k/whitepapers/pd_raid.pdf.The gist of RAID/SAN defrag is thata) Not all defrag tools are compatible with SANS/RAID arrays.b) It is the LCN numbers that get defragged rather than the physical files and this improves performance.There is a benefit in performance of having the data striped across the disks in a RAID/SAN but the LCN benefit from having contiguous blocks.Has anyone got any practical experience with this?

RE: Fragmentation 101

Craig L. Waters — Fri, 06 Jan 2006 11:33:00 GMT

We have our SQL databases on RAID sets on a SAN. I assume, therefore, that the disk defragmentation discussion is moot, since files on this hardware are fragmented by design. I wouldn't know where to begin to figure out which disks and how contiguous the files were.

Given that, one shouldn't spend too much time trying to optimize table or index storage either, other than having fillfactors that are consistent with transaction load, right?

Thanks

RE: Fragmentation 101

David Urban — Fri, 06 Jan 2006 10:12:00 GMT

The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though.

RE: Fragmentation 101

vambati — Fri, 06 Jan 2006 08:52:00 GMT

We recently migrated from SQL 7 to SQL 2000. In SQL 7 whenever a clustered index was rebuilt the NC would get rebuilt, but in SQL 2000 i do not see the NC being rebuilt. Atleast not in last 2 weeks of what i have seen. So is this an enhancement in SQL 2000 or does that mean the keys havn't changed to affect rebuild of NC?

Very good article and the dicussion is also providing lot of information.

RE: Fragmentation 101

currym — Fri, 06 Jan 2006 08:41:00 GMT

Good article! Really liked the fact that it clearly indicated problems and solutions (to include pros and cons). Thank you!

RE: Fragmentation 101

Chris Hedgate — Fri, 06 Jan 2006 03:54:00 GMT

Hm, now I do not have the experience from learning the hard way about this (not that I can recall at least). However, you did not copy-paste all what BOL says:"The DROP_EXISTING clause enhances performance when re-creating a clustered index (with either the same or a different set of keys) on a table that also has nonclustered indexes. The DROP_EXISTING clause replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and only if the keys are different.If the keys do not change (the same index name and columns as the original index are provided), the DROP_EXISTING clause does not sort the data again. This can be useful if the index must be compacted."To me, this seems to say that if you do not change the keys (e.g. run this simply to defrag the index) then the NC indexes do not need to be rebuilt at all. Even if the keys do change, the NCs only need to be rebuilt once instead of twice (one for DROP INDEX and one for CREATE INDEX). This last part is something that my article should have mentioned.Also, this is not correct:A NC index contains keys to the clustered index, i.e. the data. When you rebuild this, the data could, and probably will, move to another page. This would cause the key link to become invalid.If the keys of the clustered index stay the same then the 'links' in the NCs are not invalid even if rows move to different pages. That is the whole idea of the clustered index key, instead of the NCs pointing directly to a physical oage (with a RID), they store the key that can be used to seek the clustered index to find the row.

RE: Fragmentation 101

Crispin Proctor — Fri, 06 Jan 2006 02:43:00 GMT

To try and answer your first question:

If you only have a few rows (couple could be a few) in a table, SQL creates those pages on an extent which is shared by other pages. An extent is 64k, page 8. If you have a table with narrow rows and only a couple hundred rows, the chance is good you using a mixed extent. With this in mind, SQL may not defrag it because a) it's so small, b) there's other data sitting on the extent.

Try adding a couple thousand rows to the table (Assuming you can) and see if that changes things.

See BOL, pages and extents for more info.

To your second question, yes.

When SQL does a restore, it restores all tables. One of them being sysindexes. Sysindexes determines on what pages / extents sit. DUring a restore, SQL acquires a chuck of disk space. It then creates all the extents / pages. Once done, it starts writing out the data page by page. (or extent by extent?).

Restoring a backup will only give you contiguous OS space, not within the SQL files.

RE: Fragmentation 101

Crispin Proctor — Fri, 06 Jan 2006 02:29:00 GMT

On a whole, a good article.

One correction though.

When CREATE INDEX, WITH DROP_EXISTING is executed on a clustsered index, all non clustered indexes have to be rebuild.

A NC index contains keys to the clustered index, i.e. the data. When you rebuuild this, the data could, and probably will, move to another page. This would cause the key link to become invalid.

See BOL:

CREATE INDEX, DROP_EXISTING

"Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account"

I learnt this the hard way...

RE: Fragmentation 101

Crispin Proctor — Fri, 06 Jan 2006 02:22:00 GMT

DisKeeper claims to be able to defrag locked files.

I've used it a couple times in production.

A very nice feature it has is that it monitors the IO queue. If the queue rises, it backs off and allows the system more time on the disks. The effect is zero (Near zero) impact while doing a defrag. The con is your defrag could take forever. But you have the time...

RE: Fragmentation 101

cheriyan — Fri, 06 Jan 2006 00:09:00 GMT

Hello!!!

I have been in trouble with my SQL Query that was causing time out on the UI...Investigation with DBCC showcontig reveiled that the Scandensity for these tables are 50% or less.

But to my surprise the Scandensity was not improving with REINDEXing and DEFRAG....(Number of rows in these tables are a few hundred only) . I am not sure why is this happening....

to some how escape I tried moving these tables to new ones and saw some improvement in the Scandensity (Query to a improved slightly).

But what puzzles me is why REINDEXing and DEFRAG did't work????

Also one more question....

If I make a backup of this database and restore into a new server will that create new data/index pages?? does that have the same fragmentation as when it was backed up??? or does the RESTORE just uncompress the old pages???

Thanks

Cheriyan.

RE: Fragmentation 101

Rick Davis — Wed, 11 May 2005 11:01:00 GMT

One thing that you forgot to mention, when rebuilding the indexes you can specify a fill factor for the index. This is helpful if you have a clustered index where the new record will be inserted between existing records. Not as helpful, when all new records are inserted at the end of the table.

RE: Fragmentation 101

Chris Hedgate — Tue, 11 May 2004 12:11:00 GMT

Not any way that I can think of. Either shut down SQL Server or detach that specific database while you are defragging the files, otherwise it will not work.

RE: Fragmentation 101

john p — Tue, 11 May 2004 09:55:00 GMT

I have a server where there is heavy file fragmentation but it is quite busy and cannot be down for long periods of time. Is there any way other than shutting down SQL Server that would allow me to run a defrag on the datafiles while the the database files are in use?

RE: Fragmentation 101

Ruprect Gern — Mon, 10 May 2004 20:54:00 GMT

One of the best things about books online is the free code.

If you look up dbcc showcontig you will see a routine on the help page that retrieves the table output of the dbcc and then runs index defrag against any index fragged at or above 30%

this is easily modified to be a rebuild or a reindex.

Fragmentation 101

Chris Hedgate — Fri, 30 Apr 2004 15:20:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/fragm