SQL Server Performance Forum – Threads Archive

Unclustered index faster than clustered index?

Hi. I have a table with 338859 records. The table has a userid column
of type varchar(20) (null enabled). I use the following query: select * from table1 where userid = ‘X’ with X being a random userid. This is a slow test computer with 512 Mb ram and Windows 2000 Advanced Server. ** With no indexes the results are as follows: 33586 records. Logical reads 6797, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 5.44 SQL Server Execution Times:
CPU time = 651 ms, elapsed time = 28002 ms. ** With clustered index <userid>: 33586 records. Logical reads 632, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 0.545 SQL Server Execution Times:
CPU time = 230 ms, elapsed time = 21500 ms. ** With unclustered index <userid> (forcing the use of the index): 33586 records. Logical reads 33689, physical reads 0, read-ahead reads 0. Estimated cost according to execution plan is 206 SQL Server Execution Times:
CPU time = 461 ms, elapsed time = 13880 ms. Why is the unclustered index so much faster? I’ve repeated the query
a few times and the results stays the same, with slight variations in the time values.
I’ve implemented DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE between each query. Thanks
Leslie

Is there any column of blob type (text, ntext, image) in the table?

No, only varchar, int, bigint and datetime. The biggest varchar is 255.

Can you repeat test executing:
checkpoint
go
DBCC DROPCLEANBUFFERS
go
DBCC FREEPROCCACHE
go
before each test and let us know results (as you did before)?
Can you also post your table structure?
On top of that make sure that clustered index is actually used by checking execution plan or forcing that index.

Here is the create command of table: CREATE TABLE [Internet] (
[DateTime] [datetime] NULL ,
[SourceIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bytes] [bigint] NULL ,
[UserID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Resource] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Elapsed] [int] NULL
) ON [PRIMARY]
GO All test used with checkpoint, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. Test without index (regular table scan):
(33586 row(s) affected)
Scan count 1, logical reads 6798, physical reads 0, read-ahead reads 6823.
SQL Server Execution Times: CPU time = 581 ms, elapsed time = 22482 ms.
Total Cost of execution plan: 5.45 Test with clustered index (index is used):
(33586 row(s) affected)
Scan count 1, logical reads 632, physical reads 2, read-ahead reads 633.
SQL Server Execution Times: CPU time = 240 ms, elapsed time = 19275 ms.
Total Cost of execution plan: 0.54 Test with unclustered index (index is forced):
(33586 row(s) affected)
Scan count 1, logical reads 33689, physical reads 325, read-ahead reads 408.
SQL Server Execution Times: CPU time = 771 ms, elapsed time = 11205 ms.
Total cost of execution plan: 206

Hi Leslie, This is just a thought but up to a certain amount of rows of data it is actually quicker to perform a table scan than a clustered index seek. 33000 rows isn’t really that much data and I’ve seen table scans on 50000 rows perform faster than a clustered index seek. I’m wondering if that’s what we’re seeing here. SQL Servers optimizer thinks it is better to use a clustered index seek but the reality is that it doesn’t work out faster. Perhaps if you had several hundred thousand rows the results might be different. Don’t what others think about this possibility. Any takers [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Leslie Here is the create command of table: CREATE TABLE [Internet] ( [DateTime] [datetime] NULL , [SourceIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Service] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Bytes] [bigint] NULL , [UserID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Resource] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Site] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Elapsed] [int] NULL ) ON [PRIMARY] GO All test used with checkpoint, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. Test without index (regular table scan): (33586 row(s) affected) Scan count 1, logical reads 6798, physical reads 0, read-ahead reads 6823. SQL Server Execution Times: CPU time = 581 ms, elapsed time = 22482 ms. Total Cost of execution plan: 5.45 Test with clustered index (index is used): (33586 row(s) affected) Scan count 1, logical reads 632, physical reads 2, read-ahead reads 633. SQL Server Execution Times: CPU time = 240 ms, elapsed time = 19275 ms. Total Cost of execution plan: 0.54 Test with unclustered index (index is forced): (33586 row(s) affected) Scan count 1, logical reads 33689, physical reads 325, read-ahead reads 408. SQL Server Execution Times: CPU time = 771 ms, elapsed time = 11205 ms. Total cost of execution plan: 206 <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

Oops, I just noticed that I mis-read your initial post. You’ve got 330,000 rows, not 33,000.[8] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by SQLDBcontrol Hi Leslie, This is just a thought but up to a certain amount of rows of data it is actually quicker to perform a table scan than a clustered index seek. 33000 rows isn’t really that much data and I’ve seen table scans on 50000 rows perform faster than a clustered index seek. I’m wondering if that’s what we’re seeing here. SQL Servers optimizer thinks it is better to use a clustered index seek but the reality is that it doesn’t work out faster. Perhaps if you had several hundred thousand rows the results might be different. Don’t what others think about this possibility. Any takers [<img src=’/community/emoticons/emotion-1.gif’ alt=’

quote:This is just a thought but up to a certain amount of rows of data it is actually quicker to perform a table scan than a clustered index seek. 33000 rows isn’t really that much data and I’ve seen table scans on 50000 rows perform faster than a clustered index seek. I’m wondering if that’s what we’re seeing here. SQL Servers optimizer thinks it is better to use a clustered index seek but the reality is that it doesn’t work out faster. Perhaps if you had several hundred thousand rows the results might be different. Don’t what others think about this possibility. Any takers

There are 338859 rows in the table, 33586 rows are returned by the query. Strange behavour here is less the fact that clustered index doesn’t speed up the query as much as one would expect, more the fact that non-clustered index gives significantly better performance. I have a few hypothesis, but I won’t have time for it before late afternoon, so if anyone have an idea feell free to jump in. I’m not too confident that my hypothesis will be proven right.

How much variation do you have in the entries on the UserId column? SELECT T.UserId, COUNT(*)
FROM dbo.Internet T
GROUP BY T.UserId
ORDER BY 2 DESC

Running the query SELECT T.UserId, COUNT(*)
FROM dbo.Internet T
GROUP BY T.UserId
ORDER BY 2 DESC delivers a result of 905 userid’s with the id i’m currently using being at the
top with 33586. The top 10 values are 33586, 8276, 7107, 6130, 5721, 5654, 4067, 3911,
3634 and 3624.
The max length of the userid column is 8 characters. I know that one should look at CPU time and logical reads rather than execution time, but still there must be an explanation for this result.

quote:Running the query […….] delivers a result of 905 userid’s with the id i’m currently using being at the top with 33586.

The top UserId is on 24% of all entries, so the column offers relatively low selectivity. Bad choice to make this a clustered index, I think.

Hi Adriaan. I’m a bit slow today. Is this the correct formula?
33586 / 338859 * 100 = 9.9% How did you get 24%?

Hey, I have an idea! When you tested against non-clustered index, you don’t have any clustered index created, right?

I’ve tried creating a clustered index on say the datetime column
(which is probably a better place to use a clustered index) The result is that the CPU time of the unclustered index increased
to 1071 and the logical reads increased to 105100 where as the total
execution time decreased to about 5 seconds. My logic tells me if you’re reading 105100 pages, which is 98303 pages more than
the normal table scan, it should be slower. I’ve tried this on faster pc’s and the results were different, so I’m beginning to think that either this has to do with slower pc’s or it has something to do with the OS cache. What do you guys think?

That is exactly what I expected. Bookumark lookup are much less costly if there is no clustered index. So you have narrower index on (the most important) leaf level in case of non-clustered index and bookmark lookups are just one jump to the heap per row instead of reading 2-3 clustered index nodes in case clustered index (on different column) exists. When you search using clustered index on userID leaf rows are much wider (the whole row is on the leaf level + for 32000+ dups you have more unifier bytes), but you don’t have bookmark lookups. Anyway it explains why is non-clustered index faster with heap table then clustered one, but it’s still puzling that non-clustered index is (so much) faster then clustered one when select * is used. I have to think more about it.

Other thing that might have been the reason is more read ahead reads when clustered index is used. It would be interesting to test performance when data are already in cache. Let’s try not to clean the cache and record times after the third execution.

Unclustered index on userid and without clustered index on datetime: CPU time = 541 ms, elapsed time = 12270 ms.
which is slower than without the cache but this doesn’t mean much because
the elapsed time may vary. Unclustered index on userid and with clustered index on datetime: CPU time = 951 ms, elapsed time = 4015 ms.
Faster still, which is as it should be. P.S. I didn’t use a fillfactor with any of the clustered indexes.
At the moment I’m not doing any inserts, because I’m testing.

That’s not what I expected. I didn’t catch before that execution time had decreased. What are other statistics: logical, physical, read ahead reads? I think I’ll need to test it myself as soon as I have time for it. Does anyone have a resource explaining how exactly read ahead works?

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Leslie Hi Adriaan. I’m a bit slow today. Is this the correct formula? 33586 / 338859 * 100 = 9.9% How did you get 24%? <hr height="1" noshade id="quote"></blockquote id="quote">If you’re slow, I must be asleep. Oops, my bad![<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Anyway – if you look at those UserId entries – by any chance do they all start with the same initial, or perhaps just a small number of different initials?

Hi Adriaan I used: select distinct substring(userid,1,1) from internet and got 21 entries so that pretty much covers the alfabet.

Yes, but what about the distribution … select substring(userid,1,1), count(*) from internet group by substring(userid,1,1) order by 2 desc … as long as we’re looking for clues, why give up?[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

Sorry… Here it is: s57507
f49189
e34001
m31242
d29884
i27880
b21521
c19304
a12411
p11834
l7107
v6631
n5943
o5909
g5276
k5025
r4786
t2469
w748
h131
u61

Hi ya, are you sure it is not just parallellism to the rescue? the clustered index scan takes around 200ms of CPU time, but the non-clustered index around 700ms. This would seem to me that the non-clustered query is possibly using 4 cpus…? While this is good for a single user, it would suck for a multi-user system, since it is using around 4 times the cpu time Cheers
Twan

I’ll just bow out to Twan here (without demur).

I couldn’t agree more. The problem is that the pc has only one cpu. Can parallellism be executed with only one processor? Maybe different threads?

Hi Leslie, paralellism is limited to the number of cpus in the box. So if this is run on a single cpu box then that rules this out… odd that cpu time is higher, logical reads is higher yet total execution time is less… Cheers
Twan

Clustered index – nonclustered index – you haven’t mentioned if the table has a proper primary key. And if it has one, then was the PK perhaps created with the NONCLUSTERED keyword, or created after the table already had a clustered index defined?

No primary key existed. I tried this to see the effect of clustered vs unclustered and the theory
failed in this regard. The only conclusion that I can make is that
the execution time is worthless and one should look at the CPU time and
logical reads. The only problem is, what time affects the user? The execution time. I still think this is an anomaly but can I be certain?

You stated yourself that the test machine was a bit low on specifications, so execution time will not be too good(*). What about execution time on a "proper" computer? (*) Other processes besides SQL will be claiming OS and hardware resources at the cost of SQL performance.

The faster system delivered a better result. No index:
CPU time 297ms, Execution time 4489ms. Clustered index:
CPU time 47ms, Execution time 3356ms. Unclustered index:
CPU time 203ms, Execution time 3890ms. The logical reads and cost are about the same. At least this is consistent. So in this case clustered is better than unclustered. Still why is the other
pc so far off?

One reason could be that other processes besides SQL are claiming OS and hardware resources at the cost of SQL performance. It could be simply a lower clockspeed for the CPU – same number of CPU cycles, only there are less cycles per unit of time, so it must take more time. Anyway, there are simply less resources available for the data to be processed, so it takes longer.

Thanks Adriaan. It must be something like that. Maybe memory as well.

As you have mentioned about memory, confirm the memory settings on SQL Server.
PERFMON and PSSDIAG utility will give you more information about SQL processes with the queries. HTH Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>