Why sql server prefer index scan over the seek?

Question

Is how my table looks:

    CREATE TABLE [dbo].[ClosedTaskCustomFields](
    [ClosedTaskId] [uniqueidentifier] NOT NULL,
    [CustomFieldId] [uniqueidentifier] NOT NULL,
    [Value] [nvarchar](450) NULL
    ... 
 CONSTRAINT [PK_ClosedTaskCustomFields] PRIMARY KEY CLUSTERED 
(
    [ClosedTaskId] ASC,
    [CustomFieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And also I have such index for joining ClosedTask table:

CREATE NONCLUSTERED INDEX [IX_ClosedTaskCustomFields_ClosedTaskId] ON [dbo].[ClosedTaskCustomFields]
(
    [ClosedTaskId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

But I can't get why such query reads all ClosedTaskCustomFields table to join ClosedTask if I have index.

      select count(1) from ClosedTaskCustomFields ctcf
      join ClosedTask c on c.id = ctcf.ClosedTaskId
      where c.State = 'Rejected'
-- Result count is 50k.

plan: https://www.brentozar.com/pastetheplan/?id=SJ9Y4xFEp

Answer 1

SQL Server chooses the plan that seems cheapest based on its estimates. That means the plan you have in mind, with an index seek on both tables, would be costed higher. You can test this for yourself using a FORCESEEK hint.

Note that a higher estimated cost does not necessarily mean the plan would execute more slowly than the chosen alternative on your particular hardware.

There are many reasons the plan shown is costed lower:

1. The optimizer assumes all queries start with no data in cache.
2. Sequential I/O is costed lower than random I/O.
3. Seeks into a b-tree are assumed to be largely random, with an ongoing reduction when seeks are repeated. This attempts to account for the possibility of touching pages previously brought into memory from persistent storage for the same query.
4. SQL Server does not reduce costs on the inner side of a nested loops join when parallelism is used. This tends to disfavour parallel nested loops plans.
5. Your plan features a bitmap. This is used to reject rows that cannot join during the scan. It is the reason the scan touches 1,169,875 rows but only returns 51,307 (actual number of rows for all executions).
6. A hash join has the lowest per-row processing cost of the available join types. The extra cost of building the hash table is offset by this benefit, as well as those provided by the bitmap (not available with nested loops).

You might find the optimizer naturally chooses the seek plan with a MAXDOP 1 hint, or a higher cost threshold for parallelism in general. Seems like you have that set to the default value of 5, which many people consider too low these days.

Otherwise, you're left with the normal way to override optimizer choices—using a hint like FORCESEEK. I wouldn't bother unless the query is crucial and saving a few extra milliseconds is important.

For more on row-mode bitmaps, see my article Bitmap Magic (or… how SQL Server uses bitmap filters).

Answer 2

SQL-Server used an index seek on ClosedTask on the State index (probably because this column is used in the condition) and an index scan on ClosedTaskCustomFields.

If State was very selective, then this query would select only a small fraction of the records in each table. But probably it is not, because you have only a few states. Therefore, it is most likely cheaper to just do a scan on the index in the custom fields table and skip over the relatively small number of non-matching records, than seeking. An index seek requires several disk accesses to drill down the index tree. So, it is not necessarily cheap.

Things were different if State was very selective, i.e., if you had a lot of states.

But this is only an assumption, since I do not know the algorithm used by the query optimizer.

Answer 3

Why sql server prefer index scan over the seek?

I will try to explain in simple and basic term.

uniqueidentifier are 16 bytes.Therefore index on the uniqueidentifiercolumn uses quite a bit more space.Also uniqueidentifierare not in sequence.There is frequent page split so index may not updated.There is bit more fragmentation.Since index size is more and index data are spread across several pages here and there,optmizer will have to read almost all index page to extract data.It become costly affair for optmizer.Hence index scan over Index Seek is preferred.

Also since ClosedTaskId alone is not unique so one ClosedTaskId may have many records in table [ClosedTaskCustomFields] which are spread across several page.These pages are not in sequence.So ClosedTaskId is not selective enough in table [ClosedTaskCustomFields].