SQL Server Performance

Using partial index in a query.

Discussion in 'Performance Tuning for DBAs' started by DJSC, Mar 8, 2006.

  1. DJSC New Member


    I created a non-clustered index using 2 columns in a table. When i query the table using one column of that index on the where clause, the time cost is equal as not using the index at all. Is this possible?

    The table has over 1 million records:

    id integer PK
    id_machine integer
    db_timestamp date
    machine_timestamp date
    machine_status integer
    machine_timeout integer
    machine_event varchar2(100)

    The index is on id_machine and machine_timestamp:

    INDEX [idx_events_machine_timestamp] ON [master].[events] ([id_machine ], [machine_timestamp] desc )
    ON [INDX]

    The query is the following. I do other queries but they use the full index and there's no problem with them. Can i avoid creating another index for this specific query?

    select db_timestamp
    from master.events
    with (INDEX=idx_events_machine_timestamp)
    where machine_timeout=1
    and id_machine =520
    order by db_timestamp desc

  2. FrankKalis Moderator

  3. mmarovic Active Member

    It happened, so the answer is clear, it is possible.

    Using non-clustered index less rows are accessed because of the condition id_machine = 520 but additional bookmark lookup has to be done for each entry that satisfies the condition because the condition machine_timeout = 1 has to be checket and the column machine_timeout is not part for non-clustered index you defined. Which plan is better depends on percent of rows accessed by the condition id_machine = 520 and how does alternative (not forced) execution plan look like.
  4. DJSC New Member

    With the forced index, it does a "index seek" on "idx_events_machine_timestamp" with "bookmark lookup".

    Without the forced index it uses the "clustered index scan" on the primary key column "id" which is a auto-number sequence.

    I'm new to SQL SERVER (too obvious, isn't it!?!), but i've worked with Oracle and i notice query performance improvements when i use indexes only partially.

    Do you have a suggestion on how to improve the query?
    Or is creating another non-clustered index the only solution?


  5. mmarovic Active Member

    It may help, but it doesn't have to. It depends on data distribution. You can try to add one (machine_timeout) or two (machine_timeout and db_timestamp) as the last (two) column(s) in the index and see how much improvements you get. Covering index will be much faster for sure, but you shouldn't make decision about index design based on just one query.

Share This Page