Sql-server – Howto Identify Clustered Index Candidates

Original Post

Honestly, in your situation I'd just go ahead and cluster the [ID]. It is worrying that there appears to be no default on it, but at the same time, maybe if you're lucky, it's being populated with some form of NEWSEQUENTIALID call. If not, it isn't the end of the world, < 1MM rows shouldn't be a show stopper if the indexes / stats are being maintained.

Doing so will allow you to start focusing on reworking some filtered / include indexes and when you're done with that, maybe you'll have 4 sore spots instead of 12.

Any advice beyond that will probably result in "tip of the iceberg" classification for your problem - as you seem to be well aware, the question is really about abysmal design, rather than engine performance.

Update

I couldn't figure out a way to answer your comment clearly, so here's way too much information instead.

As it has been stated, if you have a thin, accessible, incremental columns, like an IDENTITY column, put a clustered index on that - it's a no-brainer.

However:

If no such column exists and you're looking at heaps with 10+ indexes on them ( and -still- ending up doing RID lookups!! ), just cluster the ID for now, even if it's a UNIQUEIDENTIFIER. You need, more than anything else, to clean up your storage utilization, and that's going to start with clustering -something-, if only to pull that forward fetch data back in line with rest of the row. Even if you immediately drop the clustered index afterwards, you'd at least have organized your heap for now, dramatically reducing forward pointers ( if not completely eliminating ) and ultimately reducing contention on your ( needlessly overworked ) I/O subsystem. There is literally no downside to doing this, so do it. Then you can go about figuring out why there 29 available indexes and queries still need to go RID matching.

Ignoring the index sprawl and focusing on just clustering your heaps, using a FILLFACTOR around 70 should be fine for dealing with UNIQUEIDENTIFIER values. This is because b-trees, the actual data structure SQL Server uses for indexes, tend towards around 69% node utilization under INSERT only operation of random values. This number is a mathematical truth a couple of pretty bright fellas, Lehman and Yao, proved during their research into the subject. Throwing DELETEs in sufficient quantities into the mix can lower the node utilization target, but as a general rule, a FILLFACTOR of 70 is a good place to start tuning non-sequential / random clustered indexes, since you're effectively telling SQL Server to not even bother trying to fill 30% of each page, since you won't be using that part anyway - because you won't be, mathematically.

In regards to potential page splits using a non-optimal clustering key, while it is a valid concern, if you happen to have access to a Sql Server 2012+ installation, you can follow along with this demonstration, starting with setting up an extended event session to track page splits:

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

Once that's up and running, you can set up and seed a few tables to get an idea of the impact different kinds of clustering keys under differing fill factors can have on index density and the number of page splits inserts into them can cause. For the example, I'm using a good clustering example with INTEGER and IDENTITY under FILL_FACTOR 100, a terrible clustering example with UNIQUEIDENTIFIER and non-sequential NEWID() under FILL_FACTOR 100, and then three more non-sequential UNIQUEIDENTIFIER clustering keys under FILL_FACTORs of 75, 70 and 65, each seeded with 2.5MM records.

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__PageSplitNewIDFillFactor75__Foo
        DEFAULT CONVERT( VARBINARY( 512 ), REPLICATE( 0x01, 512 ) ) FOR Foo;

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

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

    ALTER TABLE dbo.PageSplitNewIDFillFactor70
    ADD CONSTRAINT PK__PageSplitNewIDFillFactor70
        PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor70_PK )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 70 )
    ON  [PRIMARY];

    ALTER TABLE dbo.PageSplitNewIDFillFactor70
    ADD CONSTRAINT DF__PageSplitNewIDFillFactor70__PageSplitNewIDFillFactor70_PK
        DEFAULT NEWID() FOR PageSplitNewIDFillFactor70_PK;

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

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

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

    ALTER TABLE dbo.PageSplitNewIDFillFactor65
    ADD CONSTRAINT PK__PageSplitNewIDFillFactor65
        PRIMARY KEY CLUSTERED ( PageSplitNewIDFillFactor65_PK )
    WITH ( DATA_COMPRESSION = PAGE, FILLFACTOR = 65 )
    ON  [PRIMARY];

    ALTER TABLE dbo.PageSplitNewIDFillFactor65
    ADD CONSTRAINT DF__PageSplitNewIDFillFactor65__PageSplitNewIDFillFactor65_PK
        DEFAULT NEWID() FOR PageSplitNewIDFillFactor65_PK;

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

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

For index density after the initial seeding, the following query is used:

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;

And here are the density results ( notice the ~69% in the last column for the UNIQUEIDENTIFIERs ):

As you might expect, the non-sequential indexes are very, very fragmented after 2.5MM inserts and no maintenance. Moving on to check the page splits that occurred by checking the extended event we set up earlier, we can use this query:

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;

The results?

13 page splits for the good cluster, over 12500 for each of the non-sequential. This is bad in all non-sequential cases, but as originally mentioned, maintaining the indexes is an important part of making this work, so let's clean them up.

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;

And now:

Okay, with fresh indexes, the page splits are now gone, fragmentation is largely the same for each of the tables and they're conforming to their FILL_FACTORs. The non-sequential tables will put more pressure on I/O than the sequential one for size and density difference reasons, but will still perform acceptably. Now we can simulate some loads to these tables and see if page split related performance issues are likely to overcome the benefits of clustering the tables in the first place:

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

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

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

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

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

And the results:

The only table affected by page splits after another 50000 rows were inserted was the non-sequential UNIQUEIDENTIFIER under FILL_FACTOR 100. This absolutely could potentially cause performance issues. However, as shown, any of the other tested FILLFACTOR levels caused no additional page splits. I actually needed to insert another 0.5MM into the tables before I started getting page splits on the dbo.PageSplitNewIDFillFactor75 table during my testing, and that one was over the mathematical threshold anyway.

So, to reiterate, obviously, if a thin, sequential clustering key can be selected, it is the best choice. However, as I originally said, clustering a non-sequential key is a completely viable course of action, given no better option, if done responsibly. Your stated performance problems are not those of page splitting issues, they are forward fetch issues on heaps, which can be solved by adding a clustered index. So add one. If your heap has a non-clustered primary key that is a UNIQUEIDENTIFIER and that is being used throughout your software suite, cluster and FILL_FACTOR 70 that sucka, measure and adjust from there - the more time you spend putting out fires and explaining performance metrics, the less time you have to adjust the model to a more suitable form.

Don't forget to drop your extended event session!

DROP EVENT SESSION [TrackPageSplits]
ON  SERVER;
GO