Mdthbs

I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance

Is there a fast/automatic way to compromise index integrity or increase index fragmentation? Do you know any useful resource I can look at to achieve this?

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. 

Usually, Index fragmentation happens when there is Update or Insert operation happens on the table.

If you want quickly produce the issue (Index fragmentation), create an Index in your test table with less fill factor and do heavy Update or Insert operation on that table. You can work with these scripts..

You can also use CRYPT_GEN_RANDOM as I did in this answer: Filter Schema In Index Optimize Script.

You can insert data in a numeric column which has an index on it to fragment it like this:

-- Fill with random integers to create fragmentation

INSERT INTO [ProdTable] (c1, c2) VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');

GO 12800

You could also update data or convert to a string instead of a number if that's what you need.