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
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.
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
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
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
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