SQL SERVER – Performance Impact of Unique Index

Today I am going to share a story of my client who has hired me over 7 times for my most popular consulting service Comprehensive Database Performance Health Check for different servers and situations. In the latest engagement, we had a very interesting situation of Performance Impact of Unique Index.

Background Story of Unique Index

In the recent engagement, the client wanted a minimum 20% improvement in the performance of their system. We had a very interesting situation as in the first round of health check we had gone through all the low hanging fruits and already had boosted SQL Server Performance by over 100%. Now in this round, they still want to push the performance further 20%. This time we had to start looking beyond the obvious issues and dig different in their system.

It would have been easy for me to help them tune their queries and get the additional performance but they always have a very straight forward requirement – No Change in Code. This is because in their business changing code would require a large amount of deployment and release cycle and they wanted to stay away from it.

Performance Impact of Unique Index

As we had gone through the server configurations and database settings along with the indexes the option was to look at the query. However, as mentioned earlier in this blog post, I was not allowed to change the code and that lead me to look at their table structure a bit more in-depth.

While looking into the table structure, I realized that most of the indexes were not marked as Unique indexes. However, after talking to a developer and senior architect, we realized that indexes can be unique. We changed the nearly 70% indexes from non-clustered indexes to non-clustered unique indexes and noticed a lot of performance improvement. At the end of the day when my client ran the final performance test, they discovered by just changing the index type we were able to get nearly 23% of performance improvement which was over 3% from what we actually wanted.

Reproduction of the Scenario

First, we will create two tables. On one table we will create a non-clustered index and on another one, we will create a non-clustered unique index.

CREATE TABLE Table1 (ID INT IDENTITY (1,1), Col1 VARCHAR(256) DEFAULT '');
CREATE INDEX IX_NC_NOT_Unique ON Table1 (Col1);

CREATE TABLE Table2 (ID INT IDENTITY (1,1), Col1 VARCHAR(256) DEFAULT '')
CREATE UNIQUE INDEX IX_NC_Unique ON Table2 (Col1);

Now let us populate both the tables with the data

INSERT INTO Table1 (Col1)
SELECT TOP 1000000 CAST(a1.name AS VARCHAR(100))
+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(100))
FROM sys.all_columns a1
CROSS JOIN sys.all_columns a2
CROSS JOIN sys.all_columns a3
GO

INSERT INTO Table2 (Col1)
SELECT TOP 1000000 CAST(a1.name AS VARCHAR(100))
+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(100))
FROM sys.all_columns a1
CROSS JOIN sys.all_columns a2
CROSS JOIN sys.all_columns a3
GO

Once the data is populated, let us turn on the statistics.

SET STATISTICS IO ON
GO

Additionally, let us turn on the actual execution plan. Here is how you can enable the actual execution plan in SQL Server Management Studio (SSMS).

Now run the following two statements in a single batch.

SELECT DISTINCT Col1
FROM Table1
GO
SELECT DISTINCT Col1
FROM Table2
GO

At this point in time, we will observe two things 1) Statistics 2) Execution Plan

Statistics Details in Messages

Let us see details about the statistics in the messages. I have removed details which were not relevant and kept only the logical reads.

Table ‘Table1’. Scan count 1, logical reads 5612
Table ‘Table2’. Scan count 1, logical reads 5435

You can see in the statistics details the logical reads are much lesser in table 2 where we do Unique non-clustered Indexes. This means that unique non-clustered indexes really helped in reducing the IO from the disk by few hundreds of the pages.

Winner: Unique Indexes by saving IO

Execution Plan

When we noticed the execution plan, we noticed that the execution plan of table1 (non-unique index) is 55% and for the table2 (unique index) is 45%.

It is clear that unique indexes also removed an operator from the execution plan which was Stream Aggregate and also performed better in terms of overall Query Cost.

Winner: Unique Indexes by saving overall resources

Here is the code to clean up our scenario.

DROP TABLE TABLE1;
DROP TABLE TABLE2;

Final Verdict

In the various tests, I have done in the past converting existing nonclustered index a unique nonclustered index always helped with the performance.

However, SQL Server is a huge world and it is totally possible that I have not come across the scenario where unique nonclustered indexes are slower. I am sure the scenario exists somewhere and this is where I will request you to share the details with me and I will publish on this blog with due credit to you.

Well, until we find another story unique nonclustered index is a clear winner.

Reference: Pinal Dave (https://blog.sqlauthority.com)