Skip to content

Instantly share code, notes, and snippets.

@LitKnd LitKnd/index-structure.sql Secret
Last active Nov 28, 2017

Embed
What would you like to do?
/***********************************************************************
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.