Howto Identify Clustered Index Candidates

DROP EVENT SESSION [TrackPageSplits]
ON  SERVER;
GO

CREATE EVENT SESSION [TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
      AND database_id = 2 -- Watch TempDB;
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'alloc_unit_id');
GO

-- Start the Event Session Again
ALTER EVENT SESSION [TrackPageSplits]
ON SERVER
STATE=START;
GO

USE tempdb;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'PageSplitIdentity'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.PageSplitIdentity;
    CREATE TABLE dbo.PageSplitIdentity
    (
        PageSplitIdentity_PK    INTEGER IDENTITY( 1, 1 ) NOT NULL,
        Foo                     VARBINARY( 512 ) NOT NULL
    );

    ALTER TABLE dbo.PageSplitIdentity
    ADD CONSTRAINT PK__PageSplitIdentity
        PRIMARY KEY CLUSTERED ( PageSplitIdentity_PK )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 )
    ON  [PRIMARY];

    ALTER TABLE dbo.PageSplitIdentity
    ADD CONSTRAINT DF__PageSplitIdentity__Foo
        DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

    SET NOCOUNT ON;
    DECLARE @i                      INTEGER = 0;
    WHILE ( @i < 2500000 )
    BEGIN
        INSERT INTO dbo.PageSplitIdentity DEFAULT VALUES;
        SET @i = @i + 1;
    END;
    SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'PageSplitNewID'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.PageSplitNewID;
    CREATE TABLE dbo.PageSplitNewID
    (
        PageSplitNewID_PK       UNIQUEIDENTIFIER NOT NULL,
        Foo                     VARBINARY( 512 ) NOT NULL
    );

    ALTER TABLE dbo.PageSplitNewID
    ADD CONSTRAINT PK__PageSplitNewID
        PRIMARY KEY CLUSTERED ( PageSplitNewID_PK )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 100 )
    ON  [PRIMARY];

    ALTER TABLE dbo.PageSplitNewID
    ADD CONSTRAINT DF__PageSplitNewID__PageSplitNewID_PK
        DEFAULT NEWID() FOR PageSplitNewID_PK;

    ALTER TABLE dbo.PageSplitNewID
    ADD CONSTRAINT DF__PageSplitNewID__Foo
        DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

    SET NOCOUNT ON;
    DECLARE @i                      INTEGER = 0;
    WHILE ( @i < 2500000 )
    BEGIN
        INSERT INTO dbo.PageSplitNewID DEFAULT VALUES;
        SET @i = @i + 1;
    END;
    SET NOCOUNT OFF;
END;
GO

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'PageSplitNewIDFillFactor75'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.PageSplitNewIDFillFactor75;
    CREATE TABLE dbo.PageSplitNewIDFillFactor75
    (
        PageSplitNewIDFillFactor75_PK
                                UNIQUEIDENTIFIER NOT NULL,
        Foo                     VARBINARY( 512 ) NOT NULL
    );

    ALTER TABLE dbo.PageSplitNewIDFillFactor75
    ADD CONSTRAINT PK__PageSplitNewIDFillFactor75
        PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor75_PK )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 75 )
    ON  [PRIMARY];

    ALTER TABLE dbo.PageSplitNewIDFillFactor75
    ADD CONSTRAINT DF__PageSplitNewIDFillFactor75__PageSplitNewIDFillFactor75_PK
        DEFAULT NEWID() FOR PageSplitNewIDFillFactor75_PK;

    ALTER TABLE dbo.PageSplitNewIDFillFactor75
    ADD CONSTRAINT DF__PageSplit

SELECT  si.name, sips.avg_fragment_size_in_pages, 
        sips.page_count, sips.fragment_count, 
        sips.avg_fragmentation_in_percent,
        sips.avg_page_space_used_in_percent
FROM    sys.indexes si
CROSS APPLY sys.dm_db_index_physical_stats( DB_ID(), si.object_id, si.index_id, DEFAULT, 'DETAILED' ) sips 
WHERE   si.name IN ( 'PK__PageSplitIdentity', 'PK__PageSplitNewID', 
            'PK__PageSplitNewIDFillFactor75', 'PK__PageSplitNewIDFillFactor70', 'PK__PageSplitNewIDFillFactor65' )
    AND sips.index_level = 0;

SELECT  si.name, sc.split_count, si.fill_factor
FROM (  SELECT  allocation_unit_id = slot.value( '( value )[ 1 ]', 'BIGINT' ),
                split_count = slot.value( '( @count )[ 1 ]', 'BIGINT' )
        FROM (  SELECT  target_data = CONVERT( XML, target_data ) 
                FROM    sys.dm_xe_sessions xs
                INNER JOIN sys.dm_xe_session_targets xst
                    ON xs.address = xst.event_session_address
                WHERE   xs.name = 'TrackPageSplits'
                    AND xst.target_name = 'histogram' ) s
        CROSS APPLY s.target_data.nodes( 'HistogramTarget/Slot' ) n ( slot ) ) sc
INNER JOIN sys.allocation_units sau
    ON  sc.allocation_unit_id = sau.allocation_unit_id
INNER JOIN sys.partitions sp
    ON  sau.container_id = sp.partition_id
INNER JOIN sys.indexes si
    ON  sp.object_id = si.object_id
    AND sp.index_id = si.index_id;

ALTER INDEX PK__PageSplitIdentity
    ON  dbo.PageSplitIdentity REBUILD;

ALTER INDEX PK__PageSplitNewID
    ON  dbo.PageSplitNewID REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor75
    ON  dbo.PageSplitNewIDFillFactor75 REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor70
    ON  dbo.PageSplitNewIDFillFactor70 REBUILD;

ALTER INDEX PK__PageSplitNewIDFillFactor65
    ON  dbo.PageSplitNewIDFillFactor65 REBUILD;