SQL Server Performance

Unclustered index faster than clustered index?

Discussion in 'General DBA Questions' started by Leslie, Nov 30, 2005.

  1. Leslie New Member

    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
  2. mmarovic Active Member

    Is there any column of blob type (text, ntext, image) in the table?
  3. Leslie New Member

    No, only varchar, int, bigint and datetime. The biggest varchar is 255.
  4. mmarovic Active Member

    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.
  5. Leslie New Member

    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
  6. SQLDBcontrol New Member

    Hi Leslie,<br /><br />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.<br /><br />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.<br /><br />Don't what others think about this possibility. Any takers [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Leslie</i><br /><br />Here is the create command of table:<br /><br />CREATE TABLE [Internet] (<br />[DateTime] [datetime] NULL ,<br />[SourceIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Service] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Bytes] [bigint] NULL ,<br />[UserID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Resource] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Site] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Elapsed] [int] NULL <br />) ON [PRIMARY]<br />GO<br /><br />All test used with checkpoint, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.<br /><br /><b>Test without index (regular table scan):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 6798, physical reads 0, read-ahead reads 6823.<br />SQL Server Execution Times: CPU time = 581 ms, elapsed time = 22482 ms.<br />Total Cost of execution plan: 5.45<br /><br /><b>Test with clustered index (index is used):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 632, physical reads 2, read-ahead reads 633.<br />SQL Server Execution Times: CPU time = 240 ms, elapsed time = 19275 ms.<br />Total Cost of execution plan: 0.54<br /><br /><b>Test with unclustered index (index is forced):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 33689, physical reads 325, read-ahead reads 408.<br />SQL Server Execution Times: CPU time = 771 ms, elapsed time = 11205 ms.<br />Total cost of execution plan: 206<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  7. SQLDBcontrol New Member

    Oops, I just noticed that I mis-read your initial post. You've got 330,000 rows, not 33,000.[8]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by SQLDBcontrol</i><br /><br />Hi Leslie,<br /><br />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.<br /><br />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.<br /><br />Don't what others think about this possibility. Any takers [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Leslie</i><br /><br />Here is the create command of table:<br /><br />CREATE TABLE [Internet] (<br />[DateTime] [datetime] NULL ,<br />[SourceIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Service] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Bytes] [bigint] NULL ,<br />[UserID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Resource] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Site] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[Elapsed] [int] NULL <br />) ON [PRIMARY]<br />GO<br /><br />All test used with checkpoint, DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.<br /><br /><b>Test without index (regular table scan):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 6798, physical reads 0, read-ahead reads 6823.<br />SQL Server Execution Times: CPU time = 581 ms, elapsed time = 22482 ms.<br />Total Cost of execution plan: 5.45<br /><br /><b>Test with clustered index (index is used):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 632, physical reads 2, read-ahead reads 633.<br />SQL Server Execution Times: CPU time = 240 ms, elapsed time = 19275 ms.<br />Total Cost of execution plan: 0.54<br /><br /><b>Test with unclustered index (index is forced):</b><br />(33586 row(s) affected)<br />Scan count 1, logical reads 33689, physical reads 325, read-ahead reads 408.<br />SQL Server Execution Times: CPU time = 771 ms, elapsed time = 11205 ms.<br />Total cost of execution plan: 206<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  8. mmarovic Active Member

    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.
  9. Adriaan New Member

    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
  10. Leslie New Member

    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.
  11. Adriaan New Member

    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.
  12. Leslie New Member

    Hi Adriaan.

    I'm a bit slow today.

    Is this the correct formula?
    33586 / 338859 * 100 = 9.9%

    How did you get 24%?

  13. mmarovic Active Member

    Hey, I have an idea! When you tested against non-clustered index, you don't have any clustered index created, right?
  14. Leslie New Member

    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?
  15. mmarovic Active Member

    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.
  16. mmarovic Active Member

    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.
  17. Leslie New Member

    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.

  18. mmarovic Active Member

    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?
  19. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Leslie</i><br /><br />Hi Adriaan.<br /><br />I'm a bit slow today. <br /><br />Is this the correct formula?<br />33586 / 338859 * 100 = 9.9%<br /><br />How did you get 24%?<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">If you're slow, I must be asleep. Oops, my bad![<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />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?
  20. Leslie New Member

    Hi Adriaan

    I used:

    select distinct substring(userid,1,1) from internet

    and got 21 entries so that pretty much covers the alfabet.
  21. Adriaan New Member

    Yes, but what about the distribution ...<br /><br />select substring(userid,1,1), count(*)<br />from internet<br />group by substring(userid,1,1)<br />order by 2 desc<br /><br />... as long as we're looking for clues, why give up?[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  22. Leslie New Member

    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
  23. Twan New Member

    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
  24. Adriaan New Member

    I'll just bow out to Twan here (without demur).
  25. Leslie New Member

    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?
  26. Twan New Member

    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
  27. Adriaan New Member

    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?
  28. Leslie New Member

    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?
  29. Adriaan New Member

    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.
  30. Leslie New Member

    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?
  31. Adriaan New Member

    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.
  32. Leslie New Member

    Thanks Adriaan.

    It must be something like that. Maybe memory as well.
  33. satya Moderator

    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.

Share This Page