THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

Avoiding Uniqueness for Performance

In my last post, I showed how using a index unique could speed up equality seeks by around 40%.

For today’s entry, I’m going to use the same tables as last time (single BIGINT column, one table with a non-unique clustered index, and one table with a unique clustered index):

CREATE TABLE dbo.SeekTest
(
    col1    BIGINT NOT NULL,
);
GO
-- Non-unique clustered index
CREATE CLUSTERED INDEX cx
    ON dbo.SeekTest (col1)
GO
CREATE TABLE dbo.SeekTestUnique
(
    col1    BIGINT NOT NULL
);
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
    ON dbo.SeekTestUnique (col1)

Test Data

This time, instead of filling the tables with all the numbers from 1 to 5 million, we’ll add just the even numbers from 1 to 10 million.  At the risk of stating the slightly obvious, this results in tables with the same number of rows as previously, just no odd numbers:

INSERT dbo.SeekTest WITH (TABLOCKX)
    (col1)
SELECT TOP (5000000)
    2 * ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM 
    master.sys.columns AS c,
    master.sys.columns AS c2,
    master.sys.columns AS c3;
GO
INSERT dbo.SeekTestUnique WITH (TABLOCKX)
    (col1)
SELECT TOP (5000000)
    2 * ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM 
    master.sys.columns AS c,
    master.sys.columns AS c2,
    master.sys.columns AS c3;

Test Run: Non-Unique Index

The test is the same as before: join the test table to itself using a nested loops join, and count the rows returned.

Don’t be fooled by the simplistic nature of the test; I realize you rarely use loops join to self join all the rows in a table.  The point here is to check how long it takes to perform 5 million row joins – something that probably happens quite often in most production systems, either as 5 million lookups into a single very much larger table, or perhaps by running a query that does 50,000 row-joins 100 times.  Anyway, here’s the query:

SELECT 
    COUNT_BIG(*)
FROM dbo.SeekTest AS st WITH (TABLOCK)
INNER LOOP JOIN dbo.SeekTest AS st2 WITH (TABLOCK)
    ON st.col1 = st2.col1
OPTION (MAXDOP 1);

And the ‘actual’ query plan:

SNAGHTML10ed6a4

I get these results:

image

Notice the 5,000,001 scan count showing that we are performing 5 million range scans (not singleton seeks).  As far as performance goes, running this query with all the data in cache uses 9,251ms of CPU.

Test Run: Unique Index

Cool.  Now we know from last time that we can improve on this result by making the index unique, and performing singleton seeks instead of range scans.  Let’s do that then:

SELECT
    COUNT_BIG(*)
FROM dbo.SeekTestUnique AS stu WITH (TABLOCK)
INNER LOOP JOIN dbo.SeekTestUnique AS stu2 WITH (TABLOCK)
    ON stu.col1 = stu2.col1
OPTION (MAXDOP 1);

Actual plan:

SNAGHTML1102d2d

And the results are:

image

As expected, the scan count confirms we are now doing singleton seeks, and the CPU time has improved to 16,005ms.

Er, Hang On…

If you think that going from 9,251ms of CPU to 16,005ms of CPU is not exactly an improvement, you’d be right.  There’s no typo there, no inadvertent switching of the unique and non-unique examples, and no tricks.

Making the index unique really has slowed down this query by around 70%.

The explanation is so interesting, it deserves a full post of its own…so stay tuned for that.  Feel free to speculate about the cause in the meantime… in the comments below, by email, or on Twitter.

There is some evidence coming through in the comments that this behaviour is specific to x64 installations. I’d love to get some more validation of this if you have time to test on x86 and/or x64. Thanks.

Disclaimer:

In general, you will want to specify an index as UNIQUE wherever you can.  Many queries will benefit from a unique index rather than a non-unique one.  This post is very much to show that “It Always Depends” and to set the stage for the next post in this series.  Don’t run with scissors.  Warning filling may be hot.  May contain nuts… Sarcastic smile

© 2011 Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Published Thursday, August 04, 2011 2:34 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Florian Reischl said:

Hey Paul

Long time no see...

Really hope the second part of this will come up soon!

Take care

Flo

August 3, 2011 8:57 AM
 

Martin said:

Interested to see the second part but I actually get very different results to you so is it hardware or version dependant?

Table 'SeekTest'. Scan count 5000001, logical reads 15969065,

                 physical reads 0, read-ahead reads 7

SQL Server Execution Times:

CPU time = 27078 ms,  elapsed time = 27502 ms.

Table 'SeekTestUnique'. Scan count 1, logical reads 15323039,

                       physical reads 0, read-ahead reads 7

SQL Server Execution Times:

CPU time = 19219 ms,  elapsed time = 19956 ms.

@@VERSION

Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (Intel X86)   Feb 25 2011 14:22:23   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

August 3, 2011 1:01 PM
 

Paul White said:

Hi Martin,

It's possibly an x86 versus x64 thing, but it could be anything really.  Anyway, the results in the post were obtained on:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)   Jun 24 2011 13:09:43   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I have reproduced the results on:

Microsoft SQL Server 2008 (SP2) - 10.0.4285.0 (X64)   Apr 28 2011 18:27:20   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

and

Microsoft SQL Server 2005 - 9.00.5254.00 (X64)   Dec 18 2010 22:50:56   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

and

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)   Jun 24 2011 13:09:43   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Paul

August 3, 2011 1:31 PM
 

Seann Alexander said:

(1 row(s) affected)

Table 'SeekTest'. Scan count 5000001, logical reads 15995090, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 45609 ms,  elapsed time = 47693 ms.

(1 row(s) affected)

Table 'SeekTestUnique'. Scan count 1, logical reads 15331758, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 35234 ms,  elapsed time = 36514 ms.

Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86)   Sep  6 2009 01:43:32   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

------------------------------------------------------------

(1 row(s) affected)

Table 'SeekTest'. Scan count 5000001, logical reads 15995015, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 10421 ms,  elapsed time = 10422 ms.

(1 row(s) affected)

Table 'SeekTestUnique'. Scan count 1, logical reads 15331701, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 8143 ms,  elapsed time = 8141 ms.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )

August 3, 2011 2:09 PM
 

Martin said:

Hi Paul,

I've just tested on another machine and do see the behaviour from your OP.

Comparing the XML plans between the x86 instance where the unique index is faster and an x64 instance where the opposite is true I do see some differences in operator properties...

x86

Scan: Ordered="true" ScanDirection="FORWARD" ForceSeek="false"

Seek: EstimateRebinds="4942430" EstimateRewinds="57572"

x64

Scan: Ordered="false"

Seek: EstimateRebinds="5000000" EstimateRewinds="0.0153918"

August 3, 2011 2:26 PM
 

Paul White said:

Hi Seann,

Thanks for reading and contributing your results.  Interesting that it's another x86 server (though 2005 SP2 is getting a little long in the tooth now!)

Paul

August 3, 2011 2:44 PM
 

Paul White said:

Hi Martin,

I'm glad you can now get the behaviour on the x64 machine.  I hadn't anticipated a difference based on CPU architecture.  That's very interesting.

I tend to get a scan with Ordered = true, by the way, so I don't think that's an important factor.  Feel free to mail me the two plans if you'd like a second opinion on them.

Thanks for going to the trouble of testing on an x64 machine.

Paul

August 3, 2011 2:55 PM
 

Martin said:

Ah, probably not worth emailing the plans then as that was the only real difference and I was just wondering if it would relate to the eventual explanation.

Guess I'll just have to wait for part 2 then!

August 3, 2011 4:56 PM
 

mjswart said:

AAAARGH, a cliff hanger!

Can't wait til part 2

(BTW, I see similar results to yours 6365 vs 11872 on Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) )

August 4, 2011 11:19 AM
 

Paul White said:

Yeah sorry about that Michael - I did think twice before hitting you all with a 'TBC', but it really was too much for one post.  Thanks for the results too.

Paul

August 4, 2011 4:06 PM
 

ALZDBA said:

Nice short demonstration, Paul.

This post is also a great example on how one can mess up stuff with a copy/paste attitude without reflecting on the 'why has this been composed like this'.

I love it.

I hope everyone trying it out will also remove the hints and let the engine do its thing.

Thanks again for the education !

My laptop info:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)

Jun 17 2011 00:57:23

Copyright (c) Microsoft Corporation

Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

These are my test results:

Results of the queries from the article:

#########################################

Table 'SeekTest'. Scan count 5000001, logical reads 15969065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 12671 ms,  elapsed time = 12711 ms.

Table 'SeekTestUnique'. Scan count 1, logical reads 15323039, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 9141 ms,  elapsed time = 9168 ms.

Same queries without the join hints and table hints

###################################################

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SeekTest'. Scan count 2, logical reads 21060, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 6344 ms,  elapsed time = 6358 ms.

Table 'SeekTestUnique'. Scan count 2, logical reads 21060, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

  CPU time = 1781 ms,  elapsed time = 1791 ms.

August 8, 2011 7:07 AM
 

32 vs 64 confirmed here said:

SQL 9.00.5292.00 64-bit, both with and without hyperthreading; reproduced UNIQUE being slower with MAXDOP 1 and 8 both.  Rebuilding the indexes after table creation made no difference.

SQL 9.00.5259.00 32-bit; yes hyperthreading, the UNIQUE index with MAXDOP 1 was _faster_, 18 sec Unique vs. 25 sec Non-Unique.  Didn't try anything else on that server.

August 8, 2011 1:47 PM
 

Paul White said:

Thank you very much, Mr/Ms 32 vs 64 confirmed here :)

Paul

August 8, 2011 2:03 PM
 

Paul White: Page Free Space said:

The following table summarizes the results from my last two blog entries, showing the CPU time used when

August 8, 2011 4:24 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement