Sql-server – Non-Clustered Index takes much longer to create than clustered Index

clustered-indexnonclustered-indexsql-server-2016

I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create:

CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable (
    IdCol1 ASC
    ,Col2 ASC
    ,IdCol4 ASC
    ,IdCol3 ASC
    ,Col1 ASC
    ,Col6 ASC
    ) ON PS_1 (IdCol1) END

But the non-clustered index takes about 1.5 hours to create:

CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable (
    IdCol1 ASC
    ,IdCol2 ASC
    ,IdCol3 ASC
    ,IdCol4 ASC
    ,Col1 ASC
    ) INCLUDE (
    Col2
    ,DateCol1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
    )
    WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END

I'm not seeing this behavior with SQL Server 2014 but I am with SQL Server 2016. Same amount of RAM and CPU. I've tried it with out the SORT_IN_TEMPDB = ON but it has a similar problem. I've actually been seeing this in different places in my environment and all with SQL Server 2016 (Standard Edition) installations.

Best Answer

Open Questions

  • What does the table's DD look like?
  • What order are the columns in?

Clustered Index Creation

When you create a clustered index, you are actually sorting the data in the table in the order of the clustered index you are creating.

They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another time. A long time from now. When I’ve raised an army, in accordance with ancient prophecy.

Reference: Clustered Index key columns in Nonclustered Indexes (Brentozar.com)

Non-clustered Index Creation

The column order of the clustered index is totally different from the order of the columns in the non-clustered index, which has a direct impact on the creation of the index. The database engine has to search through your clustered index multiple times until it has all the information together in the correct order to create the non-clustered index. That is why it is taking so long to create the non-clustered index.

Then there is the overhead that a clustered index is always part of the non- clustered index.

Differences between SQL Server 2014 and SQL Server 2016

As for the difference in index creation on the various versions: Are you really comparing apples with apples and not with pears? Or to put it differently: Are the SQL Server 2014 servers running on identical hardware with the same CPU core frequency, with the same amount of RAM and identical MAX_DOP settings?

There some limitations/restrictions regarding index creation when using Standard or Enterprise Edition...

Enterprise vs. Standard Edition

Parallel index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2016

[...]

  • Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:
    • CREATE INDEX
    • ALTER INDEX REBUILD
    • DROP INDEX (This applies to clustered indexes only.)
    • ALTER TABLE ADD (index) CONSTRAINT
    • ALTER TABLE DROP (clustered index) CONSTRAINT

Reference: Configure Parallel Index Operations (Microsoft Docs)

If you then have a quick look at the features for SQL Server 2014 and 2016 then both editions limit MAXDOP for Standard Edition:

SQL Server 2014 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          -

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

SQL Server 2016 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          No

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

This option has a direct impact on index creation and this is probably the reason why you are seeing great differences in index creation time.

Thoughts

You might want to consider a re-design of your indexes. I'd recommend reading some of the following articles:

Additional Reading