Results 1 to 11 of 11

Thread: cpu/io cost

  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: cpu/io cost

    Hi,
    i have a table :

    id int identity primary key nonclustered,
    name varchar(50),
    catID int
    mask tinyint

    Most often I run this query:
    SELECT id,name,mask FROM shortcuts WHERE catID < 21 order by catid, fixed DESC

    I have created composite clustered index on catid, fixed.
    I thought this would be the best for performance.

    But on execution plan, there is a clustered index seek with IO cost : 7,0 and CPU cost 7,0 and overall 350%

    When I drop the clustered index, table scan is applied, and io cost and cpu cost are 0,000. How is it possible?
    The table has only 15000 rows. Table scan is faster then clust index seek? How can entire table scan have zero io and cpu cost, does it
    mean that it is in cache, whereas index seek need to read from disk?


    Thanx for help.

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    Did you bounce SQL Server, or clear the cache before you ran the second select? If you did not then the data you would have required would most likely have been in cache, which would invalidate your test.

    I find better guages of performance are "set statistics io on" - this will tell you the amount of i/o done by a query - it is well defined in BOL. Also setting the server trace on gives you a good idea about CPU usage. I stress that you must clear the cache before each test though.
    Regards
    Dbabren

  3. #3
    Join Date
    Aug 2003
    Posts
    6
    Thanx, the "SET STATISTICS IO" looks usefull, but
    how can I clear the cache? The only statement I found in BOL
    was DBCC FREEPROCCACHE, and after executing it,
    in io statictics is Physical Reads : 0, Logical Reads 120,
    so everything still comes from cache...

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    First of all, I don't know if there is such a direct method of clearing the cache (other than procedure cache with freeproccache.)

    Second, if you bounce the box before each query, all you are doing is introducing physical reads to be an additional factor. And of course, depending on your storage sub-system, it will take longer and possibly more CPU cycles. In normal prod environment physical reads are present mostly at the beginning of the business day, and rarely are observed afterwards.

    What you need to look for is logical reads and scan counts. Those are the ones that indicate performance issues resulting from lack of proper index or very low selectivity of an existing one.

    I also did not find FIXED as a field name in your table definition. Assuming it's the MASK field, you can also either try to remove FIXED from the index, or add "...and FIXED = FIXED" to your WHERE clause.

  5. #5
    Join Date
    Aug 2003
    Posts
    6
    Sorry, there is a field named "fixed" of tinyint datatype in original table.
    I forgot to write it here to the table definition.
    I want to get rows with catid < 21, ordered by fixed field,
    so I added the index. But it seems the performance has deteriorated,
    instead of any gain. I'll try to use the trace, it may help...

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How many rows are you returning? It could be that the optimizer is locating each row with the clustered index, then it has to go back and collect the other columns (id, name and mask) in order to complete the select. If you are getting a large portion of the table, this amounts to a pair of scans (one index, and one table), and you may end up with more logical reads than a straight table scan. This is also why you don't see Microsoft (or any other DB vendor) encourage the use of a lot of query hints.

    Does the real query involve a join? In the case of a join, then the query will have to look up each matching row (which would give you the use of the index), where again SQL server would have chosen a table scan.

    Best bet: Run update statistics with full scan on the table. This should give you the right statistics for the table.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    to MCrowley:

    <quote>It could be that the optimizer is locating each row with the clustered index, then it has to go back and collect the other columns (id, name and mask) in order to complete the select.</quote>

    This is not exactly how the optimizer treats data when using clustered index. Data is stored in the order of the clustered index on the same set of pages.

    Also, according to Wolffie, there is no table scan going on, instead, - it's clustered index seek, which you post does not even address:
    <quote>If you are getting a large portion of the table, this amounts to a pair of scans (one index, and one table), and you may end up with more logical reads than a straight table scan.</quote>

    The only concern that the poster has is the CPU time increase.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run the Update Statistics, because that never hurts and often helps.

    Then, I'd be curious about these things:

    1) Did updating the statistics change the execution performance?

    2) Does removing the ORDER BY clause affect clustered index -vs- table scan performance?

    3) Does the ENTIRE query run slower using the clustered index scan, or just that step?

    4) Your query looks pretty simple. Could you post the execution plans for both versions (with and without the clustered index)?

    blindman

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    to blindman:

    There is no clustered index scan nor table scan observed. It's clustered index seek, which is the indication of efficient utilization of an existing index. It's the CPU utilization that is in question.

  10. #10
    Join Date
    Aug 2003
    Posts
    6
    Thanks all for your participation.

    Here are the needed informations:


    Table has 15 000 rows, query returns about 120 rows.
    There is no join operation, the query is as simple as i wrote.

    Neither updating the statistics nor removing order by clause have
    affected the performance.

    Index is created by:
    CREATE CLUSTERED INDEX clust on shortcuts(catid, fixed)


    I cannot paste here execution plan retrieved by set showplan_all, because it would be absolutely unreadable here, so I have uploaded
    screenshot as an attachment.
    Attached Thumbnails Attached Thumbnails plans.gif  

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    to rdjabarov:
    As you can see from the query plan, the optimizer does perform a table scan when the index is not present, which is exactly what I meant. Lighten up.

    to Wolffie:
    If each run of the query contains only one statement, then that by definition that statement must require 100% of the processing time, regardless of what the graphical query plan shows. Along the same lines, no single step of a statement can take more than 100% of the statement's execution time, even if does claim to take 350%.

    This looks to me like a glitch in the query plan display. Maybe it has something to do with using non-default country settings (I noticed the comma used as a decimal separator). Unless you have evidence that one query is truly running slower than another, I wouldn't worry about it.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •