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


Fragmentation 101


Fragmentation 101

Author
Message
Chris Hedgate
Chris Hedgate
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 7
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/fragm

--
Chris Hedgate
http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Ruprect Gern
Ruprect Gern
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 1

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.


john p
john p
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 87

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?





Chris Hedgate
Chris Hedgate
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 7
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.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Rick Davis
Rick Davis
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 13
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.
cheriyan
cheriyan
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 103

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.





Crispin Proctor
Crispin Proctor
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 414

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






Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Crispin Proctor
Crispin Proctor
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 414

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






Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Crispin Proctor
Crispin Proctor
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 414

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.






Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Chris Hedgate
Chris Hedgate
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 7
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.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
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