Non clustered index on NON-SEQUENTIAL unique identifier vs. SEQUENTIAL unique identifier

If you have a non clustered index on unique identifier you might have identified a serious performance problem.

The SQL server creates for each non clustered index a separate table. By inserting a unique identifier with “NEWUID() stored procedure” each new data row has to be inserted somewhere within the table which needs a lot of performance and creates fragmentation of the table. In this case you need a function to be sure the insertion process will be made at the end of the table.

In real life you should prevent creating a non clustered index on an unique identifier but on the SQL Server side you have the option to use the “NEWSEQUENTIALID() stored procedure”. It generates an sequencial unique id based of the Windows restarting time. With this procedure you might to reach almost the same inserting performance like a non clustered index on a INT IDENTITY column. Regarding to the sequencial ascendending numbers it has the advantage that the SQL Server inserts the new rows at the end of the table.

In my example I could reduce the performance costs over 60% (see image TableIXIDReferenceTime vs. TableIXIDReferenceSequencialTime)!

Below you find the SQL-Query-Scripts which I used for the performance tests.

Creating Tables:

Inserting Data:

Print Friendly, PDF & Email