--© 2020 | ByrdNest Consulting
--Bad Page Split Example

USE AdventureWorks2017
GO
/************************************************************************************

    This script explores the clustered index at the page level for the AdventureWorks2017 
    dbo.SalesOrderDetail table.  It will delete a row within a page, then rebuild
    the whole Clustered Index.  We will look at the page before and after the row is deleted
    and the index is rebuilt.  Then the script will add back the deleted row and the affected 
    pages reviewed observing the effects of a bad page spilt.

    The SalesOrderDetail table in AdventureWorks2017 is an ideal table for this exploration
    as the SalesOrderDetailID column is an identity column and in this specific instance there
    are no gaps between rows.  First row of SalesOrderDetail has SalesOrderDetailID = 1 and
    last row has SalesOrderDetailID = 121317 with no gaps between.  Methodology here is to
    look at the table fully populated with FillFactor = 100 and then remove a row somewhere
    in the midst of the table.  Relook at the table again and then rebuild again with Fill
    Factor = 100.  Now if we insert the deleted row back in, there should be no room on the
    original page and a Bad Page Split should occur.  This script then explores that scenario
    to find the bad page split and look at the various parameters resulting from the bad
    page split.

    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 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) 
	Mar 14 2020 16:10:35, Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: )
*/

--start with clean copy of AdventureWorks2017
--    can download at https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
USE [master]
--ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'E:\SQL_Data2019\Backups\AdventureWorks2017.bak' 
    WITH  FILE = 1,  
    MOVE N'AdventureWorks2017' TO N'E:\SQL_Data2019\Data\AdventureWorks2017.mdf',  
    MOVE N'AdventureWorks2017_log' TO N'E:\SQL_Data2019\Logs\AdventureWorks2017_log.ldf',  
    NOUNLOAD,  REPLACE,  STATS = 20
ALTER DATABASE [AdventureWorks2017] SET MULTI_USER
GO

ALTER AUTHORIZATION ON DATABASE::AdventureWorks2017 TO sa;    --give ownership to sa; not me
GO
--Change Database compatibility to SS2019
ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 150
GO--00:00:07

USE AdventureWorks2017
GO

--    Let's look at AdventureWorks2017.Sales.SalesOrderDetail Table (go to SSMS, show columns,
--        and indexes (new and original)

--/************************************************************************************************
--    let's make sure Leaf-level pages are fully packed
    ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 
        ON Sales.SalesOrderDetail REBUILD
        WITH(DATA_COMPRESSION = NONE, FILLFACTOR = 100)

    I already ran REBUILD as above and backed up the database to the above location.  Each REBUILD will generally 
    change the underlying pages and their pointers.

***************************************************************************************************/


--Also show data for SalesOrderID and SalesOrderDetailID
SELECT * FROM (    SELECT TOP 4 SalesOrderID, SalesOrderDetailID        --using TOP allows me to use Order By inside the UNION SELECTs
                    FROM Sales.SalesOrderDetail
                    ORDER BY SalesOrderID, SalesOrderDetailID ASC
                UNION ALL
                SELECT TOP 4 SalesOrderID, SalesOrderDetailID
                    FROM Sales.SalesOrderDetail
                    ORDER BY SalesOrderID DESC, SalesOrderDetailID DESC ) sub
        ORDER BY 1,2

--    Note SalesOrderDetailID is monotonically increasing (with no gaps); there are 121,317 rows
--        in the table.  Also, note that SalesOrderDetailID is unique and monotonically increasing.  





--first let's look at  B-Tree for Primary Key, Clustered Index one in the same
SELECT [name],indid,reserved,used,rowcnt
    FROM sys.sysindexes
    WHERE [Name] IN ('PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID');
GO
--indid = 1            CI always has an IndID = 1
-- note rowcnt; normally will be accurate if statistics are up to date

DECLARE @IndID    INT = 1
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'), @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
-- note order, IAM_Page, INDEX_Page (One (root, 16384) at Page_Level 2 
--        and 7 (intermediate) pages at Page_Level 1)
--        and 1237 leaf level pages at level 0 
-- IAM: Index Allocation Map (and that is about all I'm going to talk about) 
--    if you want more info on IAM go to Paul Randals blog 
--        on https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/

--    Note page_type_desc:    IAM_Page
--                            Index_Page
--                            Data_Page*
--                            *Only have Data_Page in Clustered Index/Heaps
--
-- Level 2 (root( at page 16384)), root always highest level; 
--        Leaf Level always Level 0

    --almost same results as above SELECT using
--DBCC IND('AdventureWorks2017','Sales.SalesOrderDetail',1)
--GO

--    So the query has 1237 data pages (level 0) dedicated to index data; 
--    thus 121,317/1237 = ~98.1 rows/data_page

--first page in index 1 (IAM Page)
/*
--DBCC TRACEON(3604)                        -- trace 3604 needed to get results to SSMS; not for TABLERESULTS
DBCC PAGE('AdventureWorks2017',1,6928,3) WITH TABLERESULTS        
--DBCC TRACEOFF(3604)
GO
--    One result set, lots of stuff, but nothing we need for today's presentation
*/

--    So now let's look at the root page for the Clustered Index (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID)
--    Level 2 in index 1 (Index Page, --16384), root level
DBCC PAGE('AdventureWorks2017',1,16384,3) WITH TABLERESULTS    
                                --note 2nd result set (intermediate level); 3rd row, Child PageID = 17032
                                --    also note only 7 partition boundaries in root node (i.e. page not full)
GO
--two result sets; for today we will ignore first result set (metadata)
--    mainly metadata in first result set; for this presentation we'll only look at the 2nd result set.
--    look at 2nd result set; SalesOrderID (key), SalesOrderDetailID (key), ChildPageID.
--    Also note Row Size (at root and intermediate level = # of bytes for key(s) plus 7 (metadata); 
--                        leaf level = # of bytes for keys + 4 (non-clustered)

--So let's try to find a row going down the third page at the root level (3rd row is arbitrary)
--Level 1 in index 1 (Index Page, --17032 (from DBCC Page 16884)); intermediate level
DBCC PAGE('AdventureWorks2017',1,17032,3) WITH TABLERESULTS        
GO
--look at 2nd result set, 3rd row, now lower key boundary (53016,45671) and upper boundary is now 
--    (< 45790). Number of rows in this page is 120

--this is still an intermediate level result; again let's go down the third page shown from the intermediate level
--Level 1 in index 1 (Index Page, --16778 (leaf level); note all data/row here!
DBCC PAGE('AdventureWorks2017',1,16778,3) WITH TABLERESULTS        --
GO

--first row of data is SalesOrderID = 53016 with SalesOrderDetailID = 45671
--last row of data is SalesOrderID = 53062 with SalesOrderDetailID = 45789
--so there are 119 rows on this data page
--now let's delete the 79th row (2/3rds of the SalesOrderDetailSpan)
--SalesOrderID = 53049; SalesOrderDetailID = 45749 (line 1467)

--let's go back and look at the page preceeding (16777) and the page after (16779)
DBCC PAGE('AdventureWorks2017',1,16777,3) WITH TABLERESULTS        --
--    first row SalesOrderDetailID = 45552; last row SalesOrderDetailID = 45670; 119 rows
GO
DBCC PAGE('AdventureWorks2017',1,16779,3) WITH TABLERESULTS        --
--    first row SalesOrderDetailID = 45790; last row SalesOrderDetailID = 45908; 119 rows
GO

--let's just look at the row we've selected to delete
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID = 45749;
/*
SalesOrderID    SalesOrderDetailID    CarrierTrackingNumber    OrderQty    ProductID    SpecialOfferID    UnitPrice    UnitPriceDiscount    LineTotal    rowguid                                    ModifiedDate
53049            45749                NULL                    1            984            1                564.99        0.00                564.990000    BA54952F-6FB1-400F-B458-41A8F1D5D08E    2013-07-23 00:00:00.000
*/

--first save row then delete
IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp;
SELECT *
    INTO #temp
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderDetailID = 45749;
DELETE Sales.SalesOrderDetail
    WHERE SalesOrderDetailID = 45749;


--Now let's go back and climb down the B-Tree to look again at this page
DECLARE @IndID    INT = 1
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'), @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO

--page 16384 is still root
DBCC PAGE('AdventureWorks2017',1,16384,3) WITH TABLERESULTS    

--OK, still looking for the page that used to have SalesOrderDetailID = 45749


--still 3rd page in result set; lower boundary: SalesOrderID = 53131, SalesOrderDetailID = 45955
--let's look at level 1
DBCC PAGE('AdventureWorks2017',1,17032,3) WITH TABLERESULTS;    

--third row shows the page that used to have SalesOrderDetailID = 45749
DBCC PAGE('AdventureWorks2017',1,16778,3) WITH TABLERESULTS;

--looking through result set at line 1449 we find SalesOrderDetailID = 45748
--and the next row has SalesOrderDetailID = 45750 (line 1467)
--    with 118 rows on this page.

--so row is actually gone.

--now let's rebuild index to ensure data pages are fully packed
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail REBUILD
    WITH(ONLINE = ON, DATA_COMPRESSION = NONE, FILLFACTOR = 100)


--traverse the b-tree again
DECLARE @IndID    INT = 1
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'), @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO

--aha!  root page is now 30328
DBCC PAGE('AdventureWorks2017',1,30328,3) WITH TABLERESULTS    
--result set still had 1 pages at level 1
--looking for SalesOrderDetailID = 45749; it is in the third row where SalesOrderDetailID = 46036

--now look at level 1 where SalesOrderDetailID = 45749
-- again 3rd row beginning with SalesOrderID = 51751 and SalesOrderDetailID = 40425, page = 30592

DBCC PAGE('AdventureWorks2017',1,30592,3) WITH TABLERESULTS    
--looking page where SalesOrderDetailID = 45749, 10th row (page 29114) has lower boundary (52620,44739) 
--    and upper boundarary where SalesOrderDetailID < 45766

--now looking at leaf level
DBCC PAGE('AdventureWorks2017',1,29114,3) WITH TABLERESULTS    
--line 1719 has SalesOrderDetailID = 45748
--line 1737 has SalesOrderDetailID = 45750
--    with 120 rows on this page.

--now for the page split; let's put back in the row where SalesOrderDetailID = 45749
SET STATISTICS IO,TIME ON;
SET IDENTITY_INSERT Sales.SalesOrderDetail ON;
INSERT Sales.SalesOrderDetail ([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber]
                            , [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice]
                            , [UnitPriceDiscount], [rowguid], [ModifiedDate])
    SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty]
            , [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount]
            , [rowguid], [ModifiedDate]
        FROM #temp;
SET IDENTITY_INSERT Sales.SalesOrderDetail OFF;
SET STATISTICS IO,TIME OFF;


--so let's traverse the B-Tree again for the CI/PK
DECLARE @IndID    INT = 1
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'), @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO

--aha!  root page is still 30328
DBCC PAGE('AdventureWorks2017',1,30328,3) WITH TABLERESULTS    
--now looking for SalesOrderDetailID = 45749; the pointer for it is in the 3nd row (page 30592, lower boundary = 44739)

--intermediate level (1)
DBCC PAGE('AdventureWorks2017',1,30592,3) WITH TABLERESULTS    
-- now pointer for 45749 is 11th row, page 30377 (note out of sequence page)

--so let's look at pages 29114, 30377, and 29115
DBCC PAGE('AdventureWorks2017',1,29114,3) WITH TABLERESULTS    
--59 rows, lower boundary = 45656, upper boundary = 45714; note pointers lines 37,38 (below)
/**********************************************
ParentObject	Object						Field		VALUE
PAGE HEADER:	Page @0x000001B445AD2000	m_prevPage	(1:29113)
PAGE HEADER:	Page @0x000001B445AD2000	m_nextPage	(1:30377)
***********************************************/

DBCC PAGE('AdventureWorks2017',1,30377,3) WITH TABLERESULTS    
--61 rows, lower boundry = 45715, upper boundary = 45775; note pointers lines 37, 38
/***********************************************
ParentObject	Object	Field	VALUE
PAGE HEADER:	Page @0x000001B458C62000	m_prevPage	(1:29114)
PAGE HEADER:	Page @0x000001B458C62000	m_nextPage	(1:29115)
************************************************/

DBCC PAGE('AdventureWorks2017',1,29115,3) WITH TABLERESULTS    
--119 rows, lower boundry = 45776, upper boundary = 45894; note pointers lines 37, 38
/***********************************************
ParentObject	Object	Field	VALUE
PAGE HEADER:	Page @0x000001B458C62000	m_prevPage	(1:30377)
PAGE HEADER:	Page @0x000001B458C62000	m_nextPage	(1:29116)
************************************************/



--And now if we do one final rebuild now that the table is complete
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail REBUILD
    WITH(ONLINE = ON, DATA_COMPRESSION = NONE, FILLFACTOR = 100)

--so let's traverse the B-Tree again for the CI/PK
DECLARE @IndID    INT = 1
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'), @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO

--aha!  root page is now 29904
DBCC PAGE('AdventureWorks2017',1,29904,3) WITH TABLERESULTS    
--now looking for SalesOrderDetailID = 45749; the pointer for it is in the 2nd row (page 30260, lower boundary = 40507)


DBCC PAGE('AdventureWorks2017',1,30260,3) WITH TABLERESULTS    
-- now pointer for 45749 is 54th row, page 16835 (observe pages back in order by extent) 


DBCC PAGE('AdventureWorks2017',1,16835,3) WITH TABLERESULTS    
--row for 45749 is line 1251 and there are 119 rows on this page!
/***********************************************
ParentObject	Object	Field	VALUE
PAGE HEADER:	Page @0x000001B458C62000	m_prevPage	(1:16834)
PAGE HEADER:	Page @0x000001B458C62000	m_nextPage	(1:16836)
************************************************/

-- so what have we learned:
-- * the word of mouth about splitting a page in half when no
--        more room is true.  In this example we saw 120 rows in
--        the original page (with the row removed and now 59 in 
--        the original page and 61 in the new (out of order) 
--        leaf page. 
-- * a new page not in the same extent is now part of the index
--        **  when scanning, SS has to scan for a page out of order
--        **  Old page and new page now have internal fragmentation
--        ** Pointers between pages adjusted to new page
-- * 60 rows were moved to the new page