How do I intentionally fragment a SQL Server Index?
One quick way I can imagine is creating a table with UNIQUEIDENTIFIER
as a primary key and inserting lots of random values. This could be achieved using this script:
CREATE TABLE dbo.Tests (Id UNIQUEIDENTIFIER PRIMARY KEY);
GO
INSERT INTO dbo.Tests (Id)
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT NEWID()
FROM x AS x1, x AS x2, x AS x3, x AS x4, x AS x5, x AS x6;
This will generate million rows.
Knowing that NEWID()
does not guarantee any ordering, SQL Server will have to insert into random spots in table - that's going to fragment the primary key.
I wanted to make several "Ugly" indexes, so I did the following. It worked well
-- Create databases to test index job, each database is about 800MB with 100,000 GUID primary keys, in each of two tables
-- Create 6 database to test index job for DatabasesInParallel Database design based on example https://dba.stackexchange.com/q/9821/21924
--Drop last test
USE [master]
exec asp_kill_user_connections [IndexTest_1]
exec asp_kill_user_connections [IndexTest_2]
exec asp_kill_user_connections [IndexTest_3]
exec asp_kill_user_connections [IndexTest_4]
exec asp_kill_user_connections [IndexTest_5]
exec asp_kill_user_connections [IndexTest_6]
GO
DROP DATABASE [IndexTest_1]
GO
DROP DATABASE [IndexTest_2]
GO
DROP DATABASE [IndexTest_3]
GO
DROP DATABASE [IndexTest_4]
GO
DROP DATABASE [IndexTest_5]
GO
DROP DATABASE [IndexTest_6]
GO
-- create [IndexTest_1]
USE [master];
GO
CREATE DATABASE [IndexTest_1];
GO
USE IndexTest_1
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
-----------------------------------------------
-- create [IndexTest_2]
USE [master];
GO
CREATE DATABASE [IndexTest_2];
GO
USE IndexTest_2
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
------------------------------------
-- create [IndexTest_3]
USE [master];
GO
CREATE DATABASE [IndexTest_3];
GO
USE IndexTest_3
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
----------------------------------------
-- create [IndexTest_4]
USE [master];
GO
CREATE DATABASE [IndexTest_4];
GO
USE IndexTest_4
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
------------------------------------------------
-- create [IndexTest_5]
USE [master];
GO
CREATE DATABASE [IndexTest_5];
GO
USE IndexTest_5
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
--------------------------------------------
-- create [IndexTest_6]
USE [master];
GO
CREATE DATABASE [IndexTest_6];
GO
USE IndexTest_6
SET NOCOUNT ON
CREATE TABLE TestGuidA (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
CREATE TABLE TestGuidB (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))
DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000
WHILE (@BatchCounter <= 20)
BEGIN
BEGIN TRAN
DECLARE @LocalCounter INT = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidA (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @LocalCounter = 0
WHILE (@LocalCounter <= @NumRows)
BEGIN
INSERT TestGuidB (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
SET @LocalCounter +=1
END
SET @BatchCounter +=1
COMMIT
END
-------------------------------
use master
DBCC FREEPROCCACHE -- Clear plan cache for next text.