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
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;
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)
Nice one sir
Seems you have an error in the paragraph:
“You can see in the statistics details the logical reads are much lesser in table 2 where we do Unique Clustered Indexes. This means that unique clustered indexes really helped in reducing the IO from the disk by few hundreds of the pages.”
Obviously there are no clustered indexes involved here. The article deals with changing non-clustered indexes to unique non-clustered indexes.
That is correct. Thanks for pointing it out.
You said “ reducing the IO from the disk by few hundreds of the pages.”
So, is it possible that each of the two tables were contained in a different number of database pages to begin with?
How would you check to see exactly how many pages the entire table was stored in?
Maybe, because of the different index, the database decided to physically layout the storage on pages differently, Thereby resulting in fewer number of pages.