--© 2019 | ByrdNest Consulting

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

	Test script to look at cost of bad page splits
		--AdventureWorks2017Big using Sales.SalesOrderDetail
		--approach was to run 2 separate tests
			--first one was to add specific number of rows (derived from test 2)
				at end of table (identity insert); this should give good page splits
			--second test was to insert a row in middle of each page (that was intent,
				but due to table expansion was not exact)
			--and measure IOs and cpu time for each.
		--care was taken that for each test, the index had been rebuilt before the data insert
			 such that there were no bad page splits to be accounted for
		--results were interesting and will be discussed in paper

***********************************************************************/
USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Test')
	DROP DATABASE Test
GO

CREATE DATABASE Test
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Test', FILENAME = N'E:\SQL_Data2019\Data\Test.mdf' , SIZE = 1253376KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'E:\SQL_Data2019\Logs\Test_log.ldf' , SIZE = 1515520KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO


USE Test
GO

SELECT * FROM sys.databases WHERE [name] = 'Test'

--create extended event to catch bad page splits
IF EXISTS (SELECT 1 
            FROM sys.server_event_sessions 
            WHERE name = 'SQLskills_TrackPageSplits')
    DROP EVENT SESSION [SQLskills_TrackPageSplits] ON SERVER

-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION [SQLskills_TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
      AND database_id = 7 -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'alloc_unit_id');
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP Table IF EXISTS test.dbo.SalesOrderDetailBig
GO

CREATE TABLE Test.dbo.[SalesOrderDetailBig](
	SalesOrderID int NOT NULL,
	SalesOrderDetailID VARCHAR(50) NOT NULL,
	CarrierTrackingNumber nvarchar(25) NULL,
	OrderQty smallint NOT NULL,
	ProductID int NOT NULL,
	SpecialOfferID int NOT NULL,
	UnitPrice money NOT NULL,
	UnitPriceDiscount money NOT NULL,
	LineTotal  AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0))),
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
 CONSTRAINT PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED 
(
	SalesOrderID ASC,
	SalesOrderDetailID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE Test
GO

INSERT test.[dbo].[SalesOrderDetailBig]
	(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
  SELECT  SalesOrderID
	, CONVERT(VARCHAR(50),SalesOrderDetailID), CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
	FROM AdventureWorks2017Big.Sales.SalesOrderDetailBig
	ORDER BY 1,2
GO
--4852680 rows affected


USE Test
GO
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('dbo.SalesOrderDetailBig')
	                                          , @IndID,1,'Detailed')
    WHERE page_level  IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
--53,323 pages (minus IAM_Page)
--1 page root (level 2)
--338 page intermediate (level 1)
--52,984 pages leaf level (minus IAM_Page)

DECLARE @DATABASE	SYSNAME = 'Test'
    SELECT   *         
        FROM ( SELECT         
           ps.object_id objectid, 
           ps.index_id  indexid, 
           o.[name]     TableName,
           i.[name]     IndexName,
           ps.partition_number partitionnum, 
           ps.avg_fragmentation_in_percent frag,
           ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
           tab.split_count BadPageSplit,
           ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
           CASE WHEN i.Fill_Factor = 0 THEN 100 
                ELSE i.Fill_Factor END Fill_Factor,
           ps.page_count,
           ps.record_count,
           ps.forwarded_record_count,
           ps.avg_page_space_used_in_percent,
           NULL New_Frag,
           NULL New_PageSplitForInde,
           NULL New_PageAllocationCausedByPageSplit,
           NULL New_forwarded_record_count,
           0 [Redo_Flag],
           ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC)   [RowNumber]
        --  --get data for all tables/indexes
        --  SAMPLED gives same avg fragmentation as DETAILED and is much faster
        FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps 
        JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
          ON  ios.index_id         = ps.index_id
          AND ios.[object_id]      = ps.[object_id]
          AND ios.partition_number = ps.partition_number
          AND ps.index_level       = 0
        JOIN sys.indexes i
          ON  i.index_id           = ps.index_id
          AND i.[object_id]        = ps.[object_id]
        JOIN sys.objects o
          ON o.[object_id]         = i.[object_id]
        JOIN sys.partitions p
          ON  p.[object_id]           = i.[object_id]
          AND p.index_id           = i.index_id
        LEFT JOIN sys.allocation_units au
          ON au.container_id       = p.[partition_id]
        LEFT JOIN (SELECT 
                    n.value('(value)[1]', 'bigint') AS alloc_unit_id,
                    n.value('(@count)[1]', 'bigint') AS split_count
                FROM (SELECT CAST(target_data as XML) target_data
                         FROM sys.dm_xe_sessions AS s 
                         JOIN sys.dm_xe_session_targets t
                           ON s.address = t.event_session_address
                         WHERE s.name = 'SQLskills_TrackPageSplits'
                          AND t.target_name = 'histogram' ) as tab
                CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
          ON tab.alloc_unit_id = au.allocation_unit_id
        WHERE i.index_id           > 0
          AND o.[type]             = 'U'
 --         AND ps.avg_fragmentation_in_percent > 1.20   --this is rebuild condition
          AND ps.index_level       = 0 ) sub
    /*******************************************************************  
        The ORDER BY below looks at max avg_frag and then alternates the 
        next day with indexes with max page splits.  
    *********************************************************************/
    WHERE sub.RowNumber = 1
--BadPageSplit = NULL
--PageAllocationCausedByPageSplit = 339
--PageCount = 52984
--RecordCount = 4841600
SELECT count(*) from dbo.SalesOrderDetailBig
--4852680

dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
GO
/*
ObjectName			ObjectId	IndexName												IndexId	Level	Pages	Rows
SalesOrderDetailBig	1013578649	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1		0		52984	4852680
SalesOrderDetailBig	1013578649	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1		1		338		52984
SalesOrderDetailBig	1013578649	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1		2		1		338
*/

ALTER INDEX PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID ON dbo.SalesOrderDetailBig REBUILD WITH (DATA_COMPRESSION = None)
--BadPageSplit = NULL
--PageAllocationCausedByPageSplit = 169
--PageCount = 52989
dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		52989	4852680
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		160		52989
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		1		160
*/
SELECT 4852680*1.0/52983
-- 91.6 rows/page

DECLARE @DATABASE	SYSNAME = 'Test'
    SELECT   *         
        FROM ( SELECT         
           ps.object_id objectid, 
           ps.index_id  indexid, 
           o.[name]     TableName,
           i.[name]     IndexName,
           ps.partition_number partitionnum, 
           ps.avg_fragmentation_in_percent frag,
           ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
           tab.split_count BadPageSplit,
           ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
           CASE WHEN i.Fill_Factor = 0 THEN 100 
                ELSE i.Fill_Factor END Fill_Factor,
           ps.page_count,
           ps.record_count,
           ps.forwarded_record_count,
           ps.avg_page_space_used_in_percent,
           NULL New_Frag,
           NULL New_PageSplitForInde,
           NULL New_PageAllocationCausedByPageSplit,
           NULL New_forwarded_record_count,
           0 [Redo_Flag],
           ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC)   [RowNumber]
        --  --get data for all tables/indexes
        --  SAMPLED gives same avg fragmentation as DETAILED and is much faster
        FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps 
        JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
          ON  ios.index_id         = ps.index_id
          AND ios.[object_id]      = ps.[object_id]
          AND ios.partition_number = ps.partition_number
          AND ps.index_level       = 0
        JOIN sys.indexes i
          ON  i.index_id           = ps.index_id
          AND i.[object_id]        = ps.[object_id]
        JOIN sys.objects o
          ON o.[object_id]         = i.[object_id]
        JOIN sys.partitions p
          ON  p.[object_id]           = i.[object_id]
          AND p.index_id           = i.index_id
        LEFT JOIN sys.allocation_units au
          ON au.container_id       = p.[partition_id]
        LEFT JOIN (SELECT 
                    n.value('(value)[1]', 'bigint') AS alloc_unit_id,
                    n.value('(@count)[1]', 'bigint') AS split_count
                FROM (SELECT CAST(target_data as XML) target_data
                         FROM sys.dm_xe_sessions AS s 
                         JOIN sys.dm_xe_session_targets t
                           ON s.address = t.event_session_address
                         WHERE s.name = 'SQLskills_TrackPageSplits'
                          AND t.target_name = 'histogram' ) as tab
                CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
          ON tab.alloc_unit_id = au.allocation_unit_id
        WHERE i.index_id           > 0
          AND o.[type]             = 'U'
 --         AND ps.avg_fragmentation_in_percent > 1.20   --this is rebuild condition
          AND ps.index_level       = 0 ) sub
    WHERE sub.RowNumber = 1
--BadPageSplit = NULL
--PageAllocationCausedByPageSplit = 339
--PageCount = 52986
--RecordCount = 4847500

/**********************************************************************
     Reset TrackPageSplits Extended Event
***********************************************************************/
DECLARE @Command	NVARCHAR(2000);
    SET @command = N'
            -- Stop the Event Session to clear the target
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=STOP'

    PRINT @command
    EXEC sys.sp_executesql @command     

SET @command = N'
            -- Start the Event Session Again
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=START'

    PRINT @command
    EXEC sys.sp_executesql @command   
 GO   

SELECT Max(SalesOrderID),MIN(SalesOrderID) FROM Test.dbo.SalesOrderDetailBig

DROP Table IF EXISTS  #Temp

SELECT TOP 52747 SalesOrderID, SalesOrderDetailID
	INTO #Temp
	FROM test.dbo.SalesOrderDetailBig
	ORDER BY CONVERT(INT,SalesOrderDetailID) ASC
--52747 rows
ALTER TABLE #Temp
ADD CONSTRAINT PK_tTemp PRIMARY KEY (SalesOrderID,SalesOrderDetailID);
GO

SET STATISTICS IO,TIME ON
GO
INSERT test.dbo.SalesOrderDetailBig
	(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
  SELECT s.SalesOrderID+ 1302258
	, s.SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
	FROM Test.dbo.SalesOrderDetailBig s
	JOIN #Temp t
	  ON  t.SalesOrderID		= s.SalesOrderID
	  AND t.SalesOrderDetailID	= s.SalesOrderDetailID
	ORDER BY 1,2
GO
SET STATISTICS IO,TIME OFF
GO

/*
Table 'SalesOrderDetailBig'. Scan count 0, logical reads 174694, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#Temp__000000000022'. Scan count 9, logical reads 436, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 8, logical reads 109350, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(52747 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 703 ms,  elapsed time = 861 ms.
*/

DECLARE @DATABASE	SYSNAME = 'Test'
    SELECT   *         
        FROM ( SELECT         
           ps.object_id objectid, 
           ps.index_id  indexid, 
           o.[name]     TableName,
           i.[name]     IndexName,
           ps.partition_number partitionnum, 
           ps.avg_fragmentation_in_percent frag,
           ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
           tab.split_count BadPageSplit,
           ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
           CASE WHEN i.Fill_Factor = 0 THEN 100 
                ELSE i.Fill_Factor END Fill_Factor,
           ps.page_count,
           ps.record_count,
           ps.forwarded_record_count,
           ps.avg_page_space_used_in_percent,
           NULL New_Frag,
           NULL New_PageSplitForInde,
           NULL New_PageAllocationCausedByPageSplit,
           NULL New_forwarded_record_count,
           0 [Redo_Flag],
           ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC)   [RowNumber]
        --  --get data for all tables/indexes
        --  SAMPLED gives same avg fragmentation as DETAILED and is much faster
        FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps 
        JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
          ON  ios.index_id         = ps.index_id
          AND ios.[object_id]      = ps.[object_id]
          AND ios.partition_number = ps.partition_number
          AND ps.index_level       = 0
        JOIN sys.indexes i
          ON  i.index_id           = ps.index_id
          AND i.[object_id]        = ps.[object_id]
        JOIN sys.objects o
          ON o.[object_id]         = i.[object_id]
        JOIN sys.partitions p
          ON  p.[object_id]           = i.[object_id]
          AND p.index_id           = i.index_id
        LEFT JOIN sys.allocation_units au
          ON au.container_id       = p.[partition_id]
        LEFT JOIN (SELECT 
                    n.value('(value)[1]', 'bigint') AS alloc_unit_id,
                    n.value('(@count)[1]', 'bigint') AS split_count
                FROM (SELECT CAST(target_data as XML) target_data
                         FROM sys.dm_xe_sessions AS s 
                         JOIN sys.dm_xe_session_targets t
                           ON s.address = t.event_session_address
                         WHERE s.name = 'SQLskills_TrackPageSplits'
                          AND t.target_name = 'histogram' ) as tab
                CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
          ON tab.alloc_unit_id = au.allocation_unit_id
        WHERE i.index_id           > 0
          AND o.[type]             = 'U'
 --         AND ps.avg_fragmentation_in_percent > 1.20   --this is rebuild condition
          AND ps.index_level       = 0 ) sub
    WHERE sub.RowNumber = 1
--Bad Page Split = 3
--Page AllocationCausedByPageSplit = 172
--page_count = 53953

dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
GO
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1653580929	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		53593	4905427
SalesOrderDetailBig	1653580929	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		164		53593
SalesOrderDetailBig	1653580929	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		1		164
*/


/**************************************************************************
--now repeat but change insert query to cause page split at each row insert
**************************************************************************/

DROP Table IF EXISTS test.dbo.SalesOrderDetailBig
GO

CREATE TABLE Test.dbo.[SalesOrderDetailBig](
	SalesOrderID int NOT NULL,
	SalesOrderDetailID VARCHAR(50) NOT NULL,
	CarrierTrackingNumber nvarchar(25) NULL,
	OrderQty smallint NOT NULL,
	ProductID int NOT NULL,
	SpecialOfferID int NOT NULL,
	UnitPrice money NOT NULL,
	UnitPriceDiscount money NOT NULL,
	LineTotal  AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0))),
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
 CONSTRAINT PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED 
(
	SalesOrderID ASC,
	SalesOrderDetailID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE Test
GO

/**********************************************************************
     Reset TrackPageSplits Extended Event
***********************************************************************/
DECLARE @Command	NVARCHAR(2000);
    SET @command = N'
            -- Stop the Event Session to clear the target
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=STOP'

    PRINT @command
    EXEC sys.sp_executesql @command     

SET @command = N'
            -- Start the Event Session Again
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=START'

    PRINT @command
    EXEC sys.sp_executesql @command   
 GO   


INSERT test.dbo.SalesOrderDetailBig
	(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
  SELECT  SalesOrderID
	, CONVERT(VARCHAR(50),SalesOrderDetailID), CarrierTrackingNumber, OrderQty, ProductID
	, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
	FROM AdventureWorks2017Big.Sales.SalesOrderDetailBig
	ORDER BY 1,2
GO
--4852680 rows affected

USE Test
GO
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('dbo.SalesOrderDetailBig')
	                                          , @IndID,1,'Detailed')
    WHERE page_level IS NOT NULL
    ORDER BY (page_level+page_type) DESC,allocated_page_page_id
GO
--53,324 pages
--1 page root (level 2)
--338 page intermediate (level 1)
--52,985 pages leaf level

dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
GO
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1141579105	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		52984	4852680
SalesOrderDetailBig	1141579105	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		338		52984
SalesOrderDetailBig	1141579105	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		1		338
*/

SELECT count(*) from dbo.SalesOrderDetailBig
--4852680

ALTER INDEX PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID ON dbo.SalesOrderDetailBig REBUILD WITH (DATA_COMPRESSION = None)
--BadPageSplit = NULL
--PageAllocationCausedByPageSplit = 168
--PageCount = 52986
dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		52989	4852680
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		160		52989
SalesOrderDetailBig	1525580473	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		1		160
*/
SELECT 4852680*1.0/52984
-- 91.6 rows/page

DECLARE @DATABASE	SYSNAME = 'Test'
    SELECT   *         
        FROM ( SELECT         
           ps.object_id objectid, 
           ps.index_id  indexid, 
           o.[name]     TableName,
           i.[name]     IndexName,
           ps.partition_number partitionnum, 
           ps.avg_fragmentation_in_percent frag,
           ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
           tab.split_count BadPageSplit,
           ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
           CASE WHEN i.Fill_Factor = 0 THEN 100 
                ELSE i.Fill_Factor END Fill_Factor,
           ps.page_count,
           ps.record_count,
           ps.forwarded_record_count,
           ps.avg_page_space_used_in_percent,
           NULL New_Frag,
           NULL New_PageSplitForInde,
           NULL New_PageAllocationCausedByPageSplit,
           NULL New_forwarded_record_count,
           0 [Redo_Flag],
           ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC)   [RowNumber]
        --  --get data for all tables/indexes
        --  SAMPLED gives same avg fragmentation as DETAILED and is much faster
        FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps 
        JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
          ON  ios.index_id         = ps.index_id
          AND ios.[object_id]      = ps.[object_id]
          AND ios.partition_number = ps.partition_number
          AND ps.index_level       = 0
        JOIN sys.indexes i
          ON  i.index_id           = ps.index_id
          AND i.[object_id]        = ps.[object_id]
        JOIN sys.objects o
          ON o.[object_id]         = i.[object_id]
        JOIN sys.partitions p
          ON  p.[object_id]           = i.[object_id]
          AND p.index_id           = i.index_id
        LEFT JOIN sys.allocation_units au
          ON au.container_id       = p.[partition_id]
        LEFT JOIN (SELECT 
                    n.value('(value)[1]', 'bigint') AS alloc_unit_id,
                    n.value('(@count)[1]', 'bigint') AS split_count
                FROM (SELECT CAST(target_data as XML) target_data
                         FROM sys.dm_xe_sessions AS s 
                         JOIN sys.dm_xe_session_targets t
                           ON s.address = t.event_session_address
                         WHERE s.name = 'SQLskills_TrackPageSplits'
                          AND t.target_name = 'histogram' ) as tab
                CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
          ON tab.alloc_unit_id = au.allocation_unit_id
        WHERE i.index_id           > 0
          AND o.[type]             = 'U'
 --         AND ps.avg_fragmentation_in_percent > 1.20   --this is rebuild condition
          AND ps.index_level       = 0 ) sub
    WHERE sub.RowNumber = 1
--BadPageSplit = NULL
--PageAllocationCausedByPageSplit = 168
--PageCount = 52986

/**********************************************************************
     Reset TrackPageSplits Extended Event
***********************************************************************/
DECLARE @Command	NVARCHAR(2000);
    SET @command = N'
            -- Stop the Event Session to clear the target
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=STOP'

    PRINT @command
    EXEC sys.sp_executesql @command     

SET @command = N'
            -- Start the Event Session Again
            ALTER EVENT SESSION [SQLskills_TrackPageSplits]
            ON SERVER
            STATE=START'

    PRINT @command
    EXEC sys.sp_executesql @command   
 GO   

 SELECT Max(SalesOrderID),MIN(SalesOrderID) FROM Test.dbo.SalesOrderDetailBig


DROP Table IF EXISTS  #Temp

SELECT SalesOrderID, SalesOrderDetailID
	INTO #Temp
	FROM test.dbo.SalesOrderDetailBig
	WHERE CONVERT(INT,SalesOrderDetailID+46)%92 = 0
	ORDER BY CONVERT(INT,SalesOrderDetailID)
--52747 rows
ALTER TABLE #Temp
ADD CONSTRAINT PK_tTemp PRIMARY KEY (SalesOrderID,SalesOrderDetailID);
GO

SET STATISTICS IO,TIME ON
GO
INSERT test.[dbo].[SalesOrderDetailBig]
	([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID]
	, [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [rowguid], [ModifiedDate])
  SELECT  s.[SalesOrderID]
	, CONVERT(VARCHAR(50),s.[SalesOrderDetailID])+'A', [CarrierTrackingNumber], [OrderQty], [ProductID]
	, [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [rowguid], [ModifiedDate]
	FROM AdventureWorks2017Big.Sales.SalesOrderDetailBig s
	JOIN #Temp t
	  ON  t.SalesOrderID		= s.SalesOrderID
	  AND t.SalesOrderDetailID	= s.SalesOrderDetailID
GO
SET STATISTICS IO,TIME OFF
GO

/*
Table 'SalesOrderDetailBig'. Scan count 0, logical reads 449268, physical reads 0, page server reads 0, read-ahead reads 7, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#Temp__________________________________________000000000024'. Scan count 1, logical reads 160, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SalesOrderDetailBig'. Scan count 1, logical reads 52167, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(52747 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2782 ms,  elapsed time = 11409 ms.
*/

DECLARE @DATABASE	SYSNAME = 'Test'
    SELECT   *         
        FROM ( SELECT         
           ps.object_id objectid, 
           ps.index_id  indexid, 
           o.[name]     TableName,
           i.[name]     IndexName,
           ps.partition_number partitionnum, 
           ps.avg_fragmentation_in_percent frag,
           ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
           tab.split_count BadPageSplit,
           ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
           CASE WHEN i.Fill_Factor = 0 THEN 100 
                ELSE i.Fill_Factor END Fill_Factor,
           ps.page_count,
           ps.record_count,
           ps.forwarded_record_count,
           ps.avg_page_space_used_in_percent,
           NULL New_Frag,
           NULL New_PageSplitForInde,
           NULL New_PageAllocationCausedByPageSplit,
           NULL New_forwarded_record_count,
           0 [Redo_Flag],
           ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC)   [RowNumber]
        --  --get data for all tables/indexes
        --  SAMPLED gives same avg fragmentation as DETAILED and is much faster
        FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps 
        JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
          ON  ios.index_id         = ps.index_id
          AND ios.[object_id]      = ps.[object_id]
          AND ios.partition_number = ps.partition_number
          AND ps.index_level       = 0
        JOIN sys.indexes i
          ON  i.index_id           = ps.index_id
          AND i.[object_id]        = ps.[object_id]
        JOIN sys.objects o
          ON o.[object_id]         = i.[object_id]
        JOIN sys.partitions p
          ON  p.[object_id]           = i.[object_id]
          AND p.index_id           = i.index_id
        LEFT JOIN sys.allocation_units au
          ON au.container_id       = p.[partition_id]
        LEFT JOIN (SELECT 
                    n.value('(value)[1]', 'bigint') AS alloc_unit_id,
                    n.value('(@count)[1]', 'bigint') AS split_count
                FROM (SELECT CAST(target_data as XML) target_data
                         FROM sys.dm_xe_sessions AS s 
                         JOIN sys.dm_xe_session_targets t
                           ON s.address = t.event_session_address
                         WHERE s.name = 'SQLskills_TrackPageSplits'
                          AND t.target_name = 'histogram' ) as tab
                CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
          ON tab.alloc_unit_id = au.allocation_unit_id
        WHERE i.index_id           > 0
          AND o.[type]             = 'U'
 --         AND ps.avg_fragmentation_in_percent > 1.20   --this is rebuild condition
          AND ps.index_level       = 0 ) sub
    WHERE sub.RowNumber = 1
--Bad Page Split = 45023
--Page AllocationCausedByPageSplit = 376
--page_count = 97802

dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
GO
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		97802	4905427
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		366		97802
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		2		366
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	3		1		2
*/

ALTER INDEX PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID ON dbo.SalesOrderDetailBig REBUILD WITH (DATA_COMPRESSION = None)
/*SELECT * from sys.indexes i
	JOIN sys.partitions p
	  ON p.object_id = i.object_id
	  AND p.index_id = i.index_id
	where name = 'PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID'*/

dbcc showcontig ('dbo.SalesOrderDetailBig') with tableresults, all_indexes, all_levels;
GO
/*
ObjectName			ObjectId	IndexName											IndexId	Level	Pages	Rows
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	0		53565	4905427
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	1		163		53565
SalesOrderDetailBig	1685581043	PK_SalesOrderDetailBig_SalesOrderID_SalesOrderDetailID	1	2		1		163

--Bad Page Split = NULL
--Page AllocationCausedByPageSplit = 171
--page_count = 53565

*/