Fragmetation

Question

Fragmetation

Jr.DBA

Old Hand

Points: 339
More actions
August 5, 2008 at 3:34 pm

#382160

Hi,
Can any body tell me that how can we remove fragementation on a table without an Index?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

Just to clarify, you have a table that currently does not have a clustered index. You want to defragment the table.

If so, then you need to add a clustered index to the table. Adding the clustered index should defragment the table if you have enough space available in the data files.

You can drop the clustered index afterwards (if needed). I would recommend identifying what the clustered index really should be and leave it there once you are done.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

GSquared SSC Guru Points: 260824 More actions · Answer 2

You can't defrag a table that doesn't have a clustered index. Simple as that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 3

GSquared (8/6/2008)
You can't defrag a table that doesn't have a clustered index. Simple as that.

Sure you can! Put a clustered index on the table.

Ok, I know that's splitting hairs. :hehe:

Actually, throwing a clustered index on the table then removing it after its created should in theory defragment it....

GSquared SSC Guru Points: 260824 More actions · Answer 4

Jeremy Brown (8/6/2008)
GSquared (8/6/2008)
You can't defrag a table that doesn't have a clustered index. Simple as that.
Sure you can! Put a clustered index on the table.
Ok, I know that's splitting hairs. :hehe:
Actually, throwing a clustered index on the table then removing it after its created should in theory defragment it....

Okay, here's a test for you to run:

Create a heap table, with no indexes on it. Insert a bunch of data, doesn't matter how much. Tell me how fragmented the table is.

Then put a clustered index on the table, remove the clustered index, and tell me how fragmented the table is then.

Here's some sample code that should produce a pretty fragmented heap table:

create table FragTest (

ID int)

go

truncate table dbo.fragtest

go

set nocount on

go

insert into dbo.fragtest (id)

select checksum(newid())%1000000

go 1000000

declare @Start datetime

select @start = getdate()

while datediff(second, @start, getdate()) < 120

update dbo.fragtest

set id = checksum(newid())%1000000

where id = checksum(newid())%1000000

select @start = getdate()

while datediff(second, @start, getdate()) < 10

delete from dbo.fragtest

where id = checksum(newid())%1000000

go

insert into dbo.fragtest (id)

select checksum(newid())%1000000

from dbo.numbers

go 10

(My Numbers table is simply a table of the numbers from 0 to 10-thousand. If you don't have one, use any other table you want for the insert...select part.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 5

Ok, sure. I've always wondered about this anyway so using your code I ran the little test. Here are my results...

fragmentation_in_percent value before clustered index

26.0869565217391

fragmentation_in_percent value during clustered index

0.72463768115942

fragmentation_in_percent value after clustered index was dropped

11.1111111111111

GSquared SSC Guru Points: 260824 More actions · Answer 6

Pretty much as expected.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Branscome Old Hand Points: 356 More actions · Answer 7

From a maintenance standpoint please add a column (auto increment) and clustered index. Even if you never use the column in queries it will give you maintenance and optimization options you do not have now. DBCC UpdateUsage shrink file sp_updatestats Index Rebuild

Implementing these maintenance options will improve performance.

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

David Branscome (8/14/2008)
DBCC UpdateUsage shrink file sp_updatestats Index Rebuild
Implementing these maintenance options will improve performance.

The shrink file certainly won't.

The update stats is also unnecessary if the indexes are being rebuilt.

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

David Branscome Old Hand Points: 356 More actions · Answer 9

You are right, update stats is a left over from old shotgun solutions which in this case is redundant.

Shrink file has the advantage of reducing the size of the database and reducing the amount of disk that has to be covered to query a table's data.

I did not explain the rational for the shrink file. Shrink file has returned better, a more consistent reduction in database size than shrink database.

thank you for insisting on accuracy.

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

David Branscome (8/15/2008)
Shrink file has the advantage of reducing the size of the database and reducing the amount of disk that has to be covered to query a table's data.

And the disadvantage of massively fragmenting indexes and forcing the DB file to grow again (possibly causing file-level fragmentation) as soon as something is done to it (including rebuilding all the indexes that the shrink shuffled)

Essentially you're spending lots of CPU and doing lots of IOs to put as many pages into as small an area as possible, regardless of the logical order of those pages, then you're spending lots more CPU and IOs getting those pages back into the correct order, with the sort requiring empty space within the data file.

See here for a brief experiment and check the two links at the bottom

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

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: 1004406 More actions · Answer 11

Not only should you have a clustered index on a table, even if the column is an unused identity column, but you should have a unique index somewhere on the table, as well. On tables like the OP has and as someone already suggested, I'll actually throw an ID or RowNum column in and maybe make it the PK/Clustered Index. This keeps SQL Server from having to figure out what a unique row is and building that nasty little row id column in the background.

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