id int identity primary key nonclustered,
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?
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.
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...
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.
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...
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.
<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.
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.
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.
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.