Why Non-Clustered Index takes much longer to create than clustered Index?
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 without 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.
Create Clustered Index
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.
Create Nonclustered Index In Sql Server-+
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 are 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
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 - SQL Server 2016 - Editions and supported features of SQL Server 2016 - RDBMS Manageability Enterprise Standard Parallel indexed operations Yes No 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.