Sql-server – test data to generate high index fragmentation level

fragmentationindexsql server

I want to test the rebuild and reorganize operations for the indexes in sql server and for that I need to populate the db with test data that generates high fragmentation levels/percentages. Does anyone have a hint for me on how to do this? I heard about backwards insert, does this hep? If yes, how can I achieve this?
Many thanks!

Best Answer

You can use Paul Randal's script or similar to it.

The idea here is to create two similar table (of the same size) with clustered PK on identity (or other ever increasing key), then to drop the first created table and to SHRINK database.

Since SHRINK takes the pages from the end of data file and moves them to file beginning your second clustered index will be "inverted" and will be perfectly fragmented as the physical order now will be perfectly inverse to its logical order.

Why you should not shrink your data files

-- Create the 10MB filler table at the 'front' of the data file
CREATE TABLE [FillerTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'filler');
GO

-- Fill up the filler table
INSERT INTO [FillerTable] DEFAULT VALUES;
GO 1280

-- Create the production table, which will be 'after' the filler table in the data file
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1280

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Drop the filler table, creating 10MB of free space at the 'front' of the data file
DROP TABLE [FillerTable];
GO

-- Shrink the database
DBCC SHRINKDATABASE ([DBMaint2008]);
GO

-- Check the index fragmentation again
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO
Related Question