Index performance - DeFragmented to Fragmented to 90%+

• Warren Peace

  SSCertifiable

  Points: 5881

  More actions

  March 20, 2015 at 2:06 pm

  #316343

  OK, I'm aware of the performance increase of indexes, that's all fine.

  What I am looking for is how to fragment an index so I can test it's fragmented performance on an iSCSI LUN.

  I can test without an index, that's fine. I can test with a newly created index (of course that means it's not fragmented) and that's fine.

  But what I want to do is DELIBERATELY FRAGMENT (:w00t:) an index to 90%+ fragmented to test it's performance.

  I have a table with 5 million rows that I use.

  Any help is appreciated.

  Thanks

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  March 20, 2015 at 2:38 pm

  #1785479

  assuming this is not a production table, you could update all the index key values, that should give you a high level of fragmentation.

  here is an example

  CREATE TABLE frag(id int IDENTITY(1, 1),

  col varchar(10));

  go

  CREATE INDEX IX_col ON frag([col]);

  go

  INSERT INTO frag

  VALUES ('test');

  GO 10000

  ALTER INDEX ALL ON frag REBUILD;

  go

  UPDATE frag

  SET col = col + col;

  go

  SELECT avg_fragmentation_in_percent

  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('frag'), 2, NULL, 'DETAILED')

  WHERE index_level = 0;

  DROP TABLE frag;

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Warren Peace

  SSCertifiable

  Points: 5881

  More actions

  March 20, 2015 at 4:48 pm

  #1785489

  Thanks!!

• LightShip

  SSC-Addicted

  Points: 421

  More actions

  March 21, 2015 at 4:57 am

  #1785504

  dbcc shrinkfile / dbcc shrinkdatabase always work great for me when I need to frag an index 😉

• Pyman

  SSChampion

  Points: 13830

  More actions

  March 23, 2015 at 2:51 pm

  #1785819

  Simply shrink the data file and it will cause fragmentation.

  You can insert,update and delete tons of data into this table and you will notice fragmentation. Script from Robert works too.

  “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams