index-structure.sql

/***********************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
***********************************************************************/

SET XACT_ABORT, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
SET NOCOUNT ON;
GO

/**********************************
Recreate database 
**********************************/
USE master;
GO

IF DB_ID('IndexExample') IS NOT NULL
BEGIN
	ALTER DATABASE IndexExample
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

	DROP DATABASE IndexExample;
END

CREATE DATABASE IndexExample;
GO

USE master
GO
ALTER DATABASE IndexExample SET QUERY_STORE = ON
GO
ALTER DATABASE IndexExample SET QUERY_STORE (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 300, INTERVAL_LENGTH_MINUTES = 10)
GO

ALTER DATABASE IndexExample SET RECOVERY SIMPLE;
GO

/**********************************
Setup: create and fragment a disk based rowstore table
**********************************/

USE IndexExample;
GO

DROP TABLE IF EXISTS dbo.UserDatabaseTable;
GO

CREATE TABLE dbo.UserDatabaseTable (
	RowID INT NOT NULL,
	FirstName VARCHAR(1500) NOT NULL,
    CharCol char(250) NOT NULL DEFAULT ('z')
);

RAISERROR('Add one million rows to user table...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed 
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
	SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
), 
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b), 
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2), 
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3), 
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4), 
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.UserDatabaseTable (RowID, FirstName ) 
SELECT TOP (1000000) 
	ROW_NUMBER() OVER (order by (select 1)), 
	alpha.bet
FROM e6
CROSS JOIN (VALUES ('Arnold'),('Barry'),('Carrie'),('Douglas'),('Edward')
	,('Frederic'),('Gerta'),('Henry'),('Ingred'),('Jerri'),('Karen'),('Lara'),('Mandy')
	,('Nate'),('Ophelia'),('Perry'),('Queenie'),('Rex'),('Samuel'),('Theresa'),('Ursula')
	,('Victor'),('Warren'),('Xenia'),('Yorik'),('Zorro')) as alpha(bet)
GO


/* Cluster the table */
ALTER TABLE dbo.UserDatabaseTable
	ADD CONSTRAINT pk_UserDatabaseTable_RowID PRIMARY KEY CLUSTERED (RowID)
	WITH (FILLFACTOR = 100);
GO

/* Nonclustered index on FirstName */
CREATE NONCLUSTERED INDEX ix_UserDatabaseTable_FirstName_RowID_INCLUDES on 
	dbo.UserDatabaseTable (FirstName, RowID) INCLUDE (CharCol)
	WITH (FILLFACTOR = 90);
GO

/* We're going to fragment the index to spread the data out across a few more levels.
This takes ~6 seconds*/
DECLARE @letter CHAR(1), @i INT=23, @foo INT
DECLARE frag_me_outer CURSOR
FOR 
	SELECT TOP 30
		0
	FROM sys.all_columns

OPEN frag_me_outer;

FETCH NEXT FROM frag_me_outer into @foo

WHILE @@FETCH_STATUS = 0  
BEGIN 

	DECLARE frag_me_inner CURSOR
	FOR 
		SELECT alpha.bet 
		FROM (VALUES ('A'),('B'),('C'),('D'),('E')
				,('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M')
				,('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U')
				,('V'),('W'),('X'),('Y'),('Z') ) as alpha(bet);

	OPEN frag_me_inner;

	FETCH NEXT FROM frag_me_inner   
	INTO @letter;

	WHILE @@FETCH_STATUS = 0  
	BEGIN 

		UPDATE TOP (26)
			dbo.UserDatabaseTable
		SET FirstName = REPLICATE(@letter,800)
		FROM dbo.UserDatabaseTable
		WHERE RowID % @i = 0
		and LEFT(FirstName,1) <> @letter;

		SET @i = @i + 1;

		FETCH NEXT FROM frag_me_inner   
		INTO @letter;
	END   
	CLOSE frag_me_inner;  
	DEALLOCATE frag_me_inner;  

FETCH NEXT FROM frag_me_outer into @foo;
END
CLOSE frag_me_outer;  
DEALLOCATE frag_me_outer;  
GO

/**********************************
View the index physical structure
**********************************/

SELECT index_level,
	page_count, 
	cast(page_count*8./1024. as numeric (10,1)) as size_MB, 
	cast(avg_fragmentation_in_percent as numeric (4,1)) as avg_fragmentation_in_percent, 
	cast(avg_page_space_used_in_percent  as numeric (4,1)) as avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('UserDatabaseTable'), 2 ,NULL, 'detailed')
ORDER BY 1 DESC;
GO


/**********************************
Let's get the page number of the root page, at index level 5
**********************************/

SELECT 
    allocated_page_file_id, 
    allocated_page_page_id,
    previous_page_page_id,
    next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('UserDatabaseTable'), 2 ,NULL, 'detailed')
WHERE 
    page_type=2 
    and page_level = 5
    and is_allocated=1;
GO

DBCC TRACEON(3604);
GO

/*         Database    File# Page# DumpStyle*/
DBCC PAGE ('IndexExample', 1, 34329, 3);
GO



/**********************************
Let's get a list of pages at each level, so we can do this for the other levels
**********************************/

SELECT 
    page_level,
    allocated_page_file_id,
    min(allocated_page_page_id) as min_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('UserDatabaseTable'), 2 ,NULL, 'detailed')
WHERE 
    page_type=2 
    and is_allocated=1
GROUP BY page_level, allocated_page_file_id
ORDER BY page_level DESC;
GO

/*Level 4 */
DBCC PAGE ('IndexExample', 1, 32730, 3);
GO

/*Level 3 */
DBCC PAGE ('IndexExample', 1, 22494, 3);
GO

/*Level 2 */
DBCC PAGE ('IndexExample', 1, 913, 3);
GO

/*Level 1 */
DBCC PAGE ('IndexExample', 1, 865, 3);
GO


/*Level 0 */
DBCC PAGE ('IndexExample', 1, 368, 3);
GO