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!
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 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.
-- 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