/************************************************************************************
This script explores row compressed non-clustered indexes and the use case when
the Primary Key and Clustered Index are not the same (with options)
at the page level. Most likely, the page ids used here will change in your
database configuration and setup—mine rarely used same page ids even with
repetitive usage. However, the base data remained the same. Questions? Please email!
by Mike Byrd
Senior Database Engineer
ByrdNest Consulting
mbyrd@byrdnest-tx.com
www.TSQLMentoring.com
**************************************************************************************/
--What version are we running
SELECT @@VERSION
/*
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 15063: )
*/
--start with clean copy of AdventureWorkw2012
USE [master]
ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Misc\AdventureWorks2012\AdventureWorks2012.bak' WITH FILE = 1,
MOVE N'AdventureWorks2012_Data' TO N'D:\Database2017\Data\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log' TO N'D:\Database2017\Log\AdventureWorks2012_log.ldf', NOUNLOAD, REPLACE, STATS = 10
ALTER DATABASE [AdventureWorks2012] SET MULTI_USER
GO
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2012 TO sa; --give ownership to sa; not me
GO
--Change Database compatibility to SS2017
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 140
GO--00:00:07
USE AdventureWorks2012
GO
/********* Let's look at same (Part 2) Non-clustered Covering index with row compression **********/
CREATE NONCLUSTERED INDEX IDX_SalesOrderDetail_ModifiedDate3
ON Sales.SalesOrderDetail(ModifiedDate)
INCLUDE (CarrierTrackingNumber,OrderQty, ProductID)
WITH (DATA_COMPRESSION = ROW)
GO
SELECT ID,Name,Dpages,reserved,used,rowcnt,indid
FROM sys.sysindexes
WHERE [Name] IN ('IDX_SalesOrderDetail_ModifiedDate3');
GO
--indid = 5
--use following to gain info about b-tree structure for indid = 5
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 5,1,'Detailed')
WHERE page_level IS NOT NULL
--AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
--IAM_page = 23816; have one page at level 2 (24280, root)
-- and 4 intermediate index_pages at level 1;
-- 480 pages at level 0 (not counting iam_page)
--root node for row compressed IDX_SalesOrderDetail_ModifiedDate3
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,24280,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--leaf level references 4 intermediate pages; 24624 is next page for lower boundary
--Intermediate Level 1
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,24624,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--looking at result set 2, we have ModifiedDate + CI keys; if we want to traverse the NULL boundary
-- we need to look at ChildPageID = 24320; 319 pages at level 1 (intermediate nodes)
-- note 2nd boundary (2005-07-01,43681,206)
--Level 0
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,24512,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--now we are at level 0, have ModifiedDate, CI keys, and Included columns and KeyHashValue
-- we also have 205 rows at leaf level for non-clustered covering, row compressed index
-- note row compression is not shown in DBCC PAGE results
-- note last row (2005-07-01,43681,205) (ModifiedDate,SalesOrderID,SalesOrderDetailID) is
-- last row before next page (boundary)
/***** now look at use case when Primary Key and Clustered Index are not the same *****/
--Drop PK/Clustered Index because of FK depenency (not part of AdventureWorks2012; from my FK presentation)
ALTER Table dbo.Foo DROP CONSTRAINT FK_FOO_SalesOrderIDSalesOrderDetailID;
GO
--Drop Original PK (clustered index and PK the same)
ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID;
GO
--Add Non-clustered PK
ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT PK_SalesOrderDetail_SalesOrderDetailID PRIMARY KEY NONCLUSTERED (SalesOrderDetailID);
GO
--Add Clustered Index
CREATE CLUSTERED INDEX CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID,SalesOrderDetailID);
GO
--first let's look at B-Tree for Primary Key
SELECT *
FROM sys.sysindexes
WHERE [Name] IN ('PK_SalesOrderDetail_SalesOrderDetailID');
GO
--indid = 6
-- note rowcnt; normally will be accurate if statistics are up to date
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 6,1,'Detailed')
WHERE page_level IS NOT NULL
--AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
-- IAM_PAGE = 24116; root level (1) page = 30504; 214 page boundaries at page_level = 0
--root level 1
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,30816,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--note PK is used, not CI
--leaf level (0)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,29560,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--note that Clustered Index is carried along (SalesOrderID), but SalesOrderDetailID is denoted with 'key'
--now look at Clustered Index
SELECT *
FROM sys.sysindexes
WHERE [Name] IN ('CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID');
GO
--indid = 1
-- note rowcnt; normally will be accurate if statistics are up to date
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 1,1,'Detailed')
WHERE page_level IS NOT NULL
--AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
-- IAM_PAGE = 23821; root level (2) page = 528; 7 page boundaries at page_level = 1 and 1315 boundaries at level 0
--root level 2
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,29600,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--7 page boundaries; note addition of UNIQUIFIER as part of the key -- because didn't specifiy UNIQUE Clustered Index
-- also note CI used instead of PK
--intermediate level (1)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,29952,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--414 page boundaries
--leaf level (0)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,29736,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--82 rows in leaf page; UNIQUIFIER = 0 in all cases, but still carried along.
--****************let's look at the covering non-clustered index with row compression as used above *************
SELECT ID,Name,Dpages,reserved,used,rowcnt,indid
FROM sys.sysindexes
WHERE [Name] IN ('IDX_SalesOrderDetail_ModifiedDate3');
GO
--indid = 5
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 5,1,'Detailed')
WHERE page_level IS NOT NULL
AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
--IAM_page = 24112; have one page at level 2 (26984, root)
-- and 214 pages at level 0
--Level 2 (Root)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,26984,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--looking at result set 2, we stillhave ModifiedDate + CI keys + UNIQUIFER; if we want to traverse the NULL boundary
-- we need to look at ChildPageID = 28616; note 4 pages at level 1
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,28616,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--now we are at level 1 with 269 index pages with ModifiedDate, 2 CI keys and the UNIQUIFIER
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,28456,3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO
--now we are at level 0 with 200 index pages with ModifiedDate, 2 CI keys and the UNIQUIFIER plus the covering columns
--now go back and redo clustered index to be unique
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID')
DROP INDEX CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail;
GO
CREATE UNIQUE CLUSTERED INDEX CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID,SalesOrderDetailID);
GO
SELECT *
FROM sys.sysindexes
WHERE [Name] IN ('CIDX_SalesOrderDetail_SalesOrderID_SalesOrderDetailID');
GO
--indid = 1
-- note rowcnt; normally will be accurate if statistics are up to date
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 1,1,'Detailed')
WHERE page_level IS NOT NULL
--AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
-- IAM_PAGE = 24121; root level (2) page = 6224; 7 page boundaries at page_level = 1 and 1237 boundaries at level 0
--root level 2
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,6224,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--7 page boundaries; note UNIQUIFIER is now missing; keys reflect the unique clustered index; ChildPageID = 11296 for NULL, NULL boundary
--intermediate level (1)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,11296,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--414 page boundaries (as opposed to 364 with UNIQUIFIER)
--leaf level (0)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,10672,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--80 rows in leaf page; no uniqifier, one more row in page
--now let's look at the covering non-clustered index as used before
CREATE NONCLUSTERED INDEX IDX_SalesOrderDetail_ModifiedDate
ON Sales.SalesOrderDetail(ModifiedDate)
INCLUDE (CarrierTrackingNumber,OrderQty, ProductID)
GO
SELECT ID,Name,Dpages,reserved,used,rowcnt,indid
FROM sys.sysindexes
WHERE [Name] IN ('IDX_SalesOrderDetail_ModifiedDate');
GO
--indid = 6
SELECT OBJECT_NAME(object_id), index_id, partition_id,allocated_page_page_id, is_iam_page, page_type
,page_type_desc, page_level,next_page_file_id,next_page_page_id,previous_page_file_id
,previous_page_page_id, is_page_compressed
FROM sys.dm_db_database_page_allocations (DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 6,1,'Detailed')
WHERE page_level IS NOT NULL
--AND page_level = 0
ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
--IAM_page = 5204; have one page at level 2 (25592, root)
-- and 6 intermediate index_pages at level 1; 637 pages at level 0
--Level 2 (Root)
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,25592,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--looking at result set 2, we have ModifiedDate + CI keys; if we want to traverse the NULL boundary
-- we need to look at ChildPageID = 27128; note 6 pages at level 1
--Intermediate Level 1
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,27128,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--again, looking at result set 2 and staying on the CI NULL,NULL boundary we have ChildPageID = 26952;
-- now we have 286 pages at level 1
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,26952,3) WITH TABLERESULTS
GO
DBCC TRACEOFF(3604)
GO
--now we are at level 0, have ModifiedDate, CI keys, and Included columns and KeyHashValue
-- we also have 84 rows at leaf level for non-clustered covering index
Leave a Reply