Index Fragmentation

I have been tasked with a obtaining a method of manually creating Clustered Index fragmentation for testing purposes. Does anyone have a script that would cause fragmentation but would still keep the table contents the same. Thanks.

I don't have a script, but something like this for each table...

You have to reload it I beleive. Can't rebuild the clusterered index a different order and set the order back, as it would defrag at that point...so get the data off to a temp table...

select * into #tableName from tableName

Then you need to reload it in chunks, with an order by that goes against the clustered index column order... If you just try one insert from that #tableName, it won't frag enough, SQL will get it mostly in clustered order as it loads.. but if you break up the loads into unordered batches it'll frag the clusterered index... You'll need to deal with PK/FK's and copy teh data off to a temp table , then bring it back something like this, in small batches, until all teh data is back in...

Code Snippet

insert into tableName

select top 1000 *

from #tableName t1

where not exists

(select *

from tableName t2

where t1.pkcolumn = t2.pkcolumn)

order by non_clusterd_columns desc

You could generate that script if you have numerous table to reload.... I was looking to see if there is a command on a CREATE INDEX to just not rebuild the clustered index if you change it, but didn't see it.. might be a way though someone else knows......... Have fun.... Bruce

|||

This seems like it will do the job. The only issue i have is that the table does not have a unique ID. So any ideas on loading in 1000 row chunks would be appreciated.

Thanks

|||

One way of doing that... you could add a row number to the # table, like this...

Code Snippet

select

ROW_NUMBER()OVER(ORDERBYnon_clustered_column)AS'ID',*

into #tableName

from tableName

order by non_clustered_column

so your # table would then be setup with an ID and in jumbled order .... could then loop thru that by ID, in batches of 1000 and it would be loading in scrambled order. Do enough batches so it scrambles it good, the lower the batch row count the better to cause fragmentation... Bruce

|||Many thanks for this. Just need to figure out the best way to insert in 1000 row batches now.|||I am stuggling to find a method of importing 1000 row batches. What would be the best method, if i have added the ID column to the table?|||

Once you have your temp table, and the ID field in it... you could do some type of a LOOP like this...

Code Snippet

while(1=1)

BEGIN

INSERTINTO yourTable

selecttop 1000 *

from #tempTable t1

wherenotexists

(select*

from yourTable t2

where t1.ID= t2.ID)

orderby nonclustereColumn desc

if(@.@.rowcount= 0 )break

END

Might have to play some games with the ID not being in your base table. Like, add the column, then drop it after you load... But you said you have a clustered index, so just do the match based on all clustered index columns... and don't bother with the new ID column?!?...

Bruce|||Thanks Bruce