SQL Server Performance

Why Index scan?

Discussion in 'T-SQL Performance Tuning for Developers' started by mjschwenger, Dec 1, 2005.

  1. mjschwenger New Member

    I have 2 identical queries running on 2 identical tables with almost the same number of rows in all 4 tables. The first one uses Index seek and runs for 3 sec. The second one - take 5 min because of the Index scan it does. Of course, I have indexes on both tables on attr/num_type_id and I did rebuild all indexes, updated statistics.
    Please, help me to find out why is doing that.
    First BAD case:
    declare @P1 int
    declare @P2 int
    set @P2=4
    declare @P3 int
    set @P3=1
    declare @P4 int
    set @P4=-1
    exec sp_cursoropen @P1 output,
    N'SELECT attribute.attr_id AS attr_id,
    attribute.dsrc_acct_id AS dsrc_acct_id,
    attribute.attr_type_id AS attr_type_id,
    attribute.attr_value AS attr_value,
    attribute.sys_delete_dt AS sys_delete_dt,
    attr_type.attr_type AS attr_type
    FROM attribute, attr_type
    WHERE attribute.attr_type_id = attr_type.attr_type_id
    AND dsrc_acct_id = 7056 AND attr_id <= 11997
    AND (sys_delete_dt IS NULL OR sys_delete_dt > 0)
    ORDER BY attr_type_id, attr_value'
    , @P2 output, @P3 output, @P4 output
    select @P1, @P2, @P3, @P4
    -- Fetch the next 3 lines
    EXEC sp_cursorfetch @P1, 2, 1, 256
    -- Close the cursor
    EXEC sp_cursorclose @P1

    GOOD case:
    declare @P1 int
    declare @P2 int
    set @P2=4
    declare @P3 int
    set @P3=1
    declare @P4 int
    set @P4=-1
    exec sp_cursoropen @P1 output,
    N'SELECT nums.num_id AS num_id,
    nums.dsrc_acct_id AS dsrc_acct_id,
    nums.num_type_id AS num_type_id,
    num_type.num_type AS num_type
    FROM nums, num_type
    WHERE nums.num_type_id = num_type.num_type_id
    AND dsrc_acct_id = 10115 AND num_id <= 0
    AND (sys_delete_dt IS NULL OR sys_delete_dt >0)
    ORDER BY num_type_id, num_value'
    , @P2 output, @P3 output, @P4 output
    select @P1, @P2, @P3, @P4
    -- Fetch the next 3 lines
    EXEC sp_cursorfetch @P1, 2, 1, 256
    -- Close the cursor
    EXEC sp_cursorclose @P1

    Thanks a lot,mj
  2. druer New Member

    An index seek is the obviously the pay-dirt of performance. It can just pop through the values and find your value directly.

    An index scan is the "I'm not as bad as a table scan, but I might as well be." It likely means that you have a field in your where clause that is not the first field for the index.

    Does the index for nums.num_type_id have num_type_id as the field?
  3. mjschwenger New Member

    These are the 2 indexes on num_type table:
    CREATE INDEX [IX_JOIN] ON [dbo].[NUM_TYPE]([NUM_TYPE_ID], [UNIQUE_FLAG], [MATCH_CRITERIA]) ON [PRIMARY]
    GO
    CREATE UNIQUE INDEX [IX_NUM_TYPE] ON [dbo].[NUM_TYPE]([NUM_TYPE]) ON [PRIMARY]
    GO
    and the same on attr_type
    Thanks a lot,mj
  4. mmarovic Active Member

    What are indexes on table attribute? How many rows do you have in each table?
  5. druer New Member

    Also need to see indexes for tables:
    Attribute, Attr_Type and Nums

    Then tell us which index is reporting the index seek and which is reporting the index scan

Share This Page