SQL Server Performance Forum – Threads Archive

Why Index scan?

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

---

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?

---

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

---

What are indexes on table attribute? How many rows do you have in each table?

---

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

---

]]>