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.
