HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Manually create index fragmentation

Submitted by: @import:stackexchange-dba··
0
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

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.