Manually create index fragmentation

Question

Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table? I need this for the purpose of a index rebuild benchmark. In order to measure the time required for specifig fragmentation rates of the same table / index, I have to re-create specific fragmentation levels I want to try.

It's very time consuming and difficult to create this fragmentation with some more or less ramdom table updates. Any ideas?

UPDATE: This is my test table. It actually has about 540K rows,

USE [Test]
GO

/****** Object:  Table [dbo].[tFrag2]    Script Date: 12/22/2015 15:33:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tFrag2](
    [a] [int] NULL,
    [b] [varchar](1000) COLLATE Latin1_General_CI_AS NULL,
    [c] [varchar](1000) COLLATE Latin1_General_CI_AS NULL,
    [d] [decimal](18, 0) NULL,
    [e] [int] NULL,
    [uid] [uniqueidentifier] NOT NULL,
    [dt1] [datetime] NULL,
    [dt2] [datetime] NULL,
    [dt3] [datetime] NULL,
    [dt4] [datetime] NULL,
    [dt5] [datetime] NULL,
    [dt6] [datetime] NULL,
    [dt7] [datetime] NULL,
    [dt8] [datetime] NULL,
    [dt9] [datetime] NULL,
    [nv1] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv2] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv8] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv3] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv4] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv5] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv6] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [nv7] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
    [lfd] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tFrag2] PRIMARY KEY CLUSTERED 
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_a]  DEFAULT ((123123)) FOR [a]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_b]  DEFAULT ('ccccccccccccccccccc4444444444444444444444444') FOR [b]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_c]  DEFAULT ('ffffffffffffffffffffffffffffffffffffffffffffffff') FOR [c]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_d]  DEFAULT ((7)) FOR [d]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_e]  DEFAULT ((777)) FOR [e]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag_uid2]  DEFAULT (newid()) FOR [uid]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt1]  DEFAULT (getdate()) FOR [dt1]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt2]  DEFAULT (getdate()) FOR [dt2]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt3]  DEFAULT (getdate()) FOR [dt3]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt4]  DEFAULT (getdate()) FOR [dt4]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt5]  DEFAULT (getdate()) FOR [dt5]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt6]  DEFAULT (getdate()) FOR [dt6]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt7]  DEFAULT (getdate()) FOR [dt7]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt8]  DEFAULT (getdate()) FOR [dt8]
GO

ALTER TABLE [dbo].[tFrag2] ADD  CONSTRAINT [DF_tFrag2_dt9]  DEFAULT (getdate()) FOR [dt9]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxxx') FOR [nv1]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv2]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv8]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv3]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv4]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv5]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv6]
GO

ALTER TABLE [dbo].[tFrag2] ADD  DEFAULT ('xxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyxx') FOR [nv7]
GO

Answer 1

For one index, no. For indexes within a database, yes: create a table and do a loop to create a bunch of records and grow the database. Then drop the table. Now, shrink the data file (very, very stupid according to Paul Randal).

Answer 2

To cause fragmentation you can use GUID columns, which will be indexed. Then fill it with newid() function, which randomly generates GUIDs. Indexed GUID column (uniqueidentifier) will be then fragmented:

create table FragmentedIndex (
    Id uniqueidentifier primary key,
    SomeOtherData varchar(50)
);
go

insert into FragmentedIndex values (newid(), 'some data');
go 10000

In my case, I got almost 100% fragmentation, so pages were completely out of index order:

Answer 3

I quickly typed this thing... this sucker really works, but... is slow. Any thoughts?

(dbo.FragStat is a view that queries the fragmentation value for this table)

Create procedure dbo.fragIt
     @FragDest int = 10
as
SET Statistics IO OfF
SET Statistics Time OfF
SET nocount on

declare @fragPercent int = 0

declare @cntr int = 0
While @FragDest > @fragPercent 
Begin
    select @fragPercent = avg_fragmentation_in_percent from dbo.FragStat Where NameOfTable = 'tFrag2' AND ALLOC_unit_type_desc = 'IN_ROW_DATA'

    set @cntr = @cntr + DATEPART(MS, GETDATE()) + 1000
    Update tFrag2 SET nv1 = REPLICATE('X', 1000), nv2 = REPLICATE('Y', 1000), nv3 = REPLICATE('Y', 1000) 
    WHERE LFD = @cntr or LFD = @cntr + 135 OR LFD = @cntr + 240 OR LFD = @cntr + 545 OR LFD = @cntr + 657 OR LFD = @cntr + 784 OR LFD = @cntr + 854

End
print 'destination frag of ' + cast(@FragDest as varchar(3)) + ' reached: ' + cast(@fragPercent as varchar(3))

Answer 4

Fragmentation occurs when the storage engine can't physically place a row in its correct logical position. So you need a table with no internal space, then insert rows in the "middle".

The easiest way to fill pages is to ensure only one row will fit

create table T (
id int,                  -- primary key & clustered; fill factor 100%
c1 char (5000) not null  -- fixed length, over half the size of a page
)

Write as many rows as you need; a few hundred thousand should do it. Keep id sequential to avoid fragmentation a this stage. Leave gaps for new values, however.

Now INSERT rows to achieve the desired fragmentation. Randomly generate id values between the current min and max. This can be done iteratively, or set-wise by selecting the top n from the current rows and incrementing the id to fit in the gaps reserved.

A target row count can be achieved by starting with a smaller initial set or deleting rows randomly.