snippetsqlMinor
Manually create index fragmentation
Viewed 0 times
manuallyfragmentationcreateindex
Problem
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 COLLATE Latin1_General_CI_AS NULL,
[c] varchar COLLATE Latin1_General_CI_AS NULL,
[d] decimal 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 COLLATE Latin1_General_CI_AS NULL,
[nv2] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv8] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv3] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv4] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv5] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv6] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv7] nvarchar 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 [PRIMA
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 COLLATE Latin1_General_CI_AS NULL,
[c] varchar COLLATE Latin1_General_CI_AS NULL,
[d] decimal 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 COLLATE Latin1_General_CI_AS NULL,
[nv2] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv8] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv3] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv4] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv5] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv6] nvarchar COLLATE Latin1_General_CI_AS NULL,
[nv7] nvarchar 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 [PRIMA
Solution
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).
Context
StackExchange Database Administrators Q#124336, answer score: 3
Revisions (0)
No revisions yet.