1

Comming from an MySQL background I'm having difficulties to understand what's wrong with the following setup.

I have two tables variable and dimension

Both have a primary key, variable furthermore has a foreign key to dimension named dimension_instance_1_uid, on which an index was created.

When I execute a query like this 

SELECT this_.name, dimensioni4_.name FROM dbo.variable this_ INNER JOIN dbo.dimension_instance dimensioni4_ -- even with index hint nothing changes... -- WITH (INDEX(PK_dimension_instance)) ON this_.dimension_instance_1_uid = dimensioni4_.UID

it seems as if the index isn't used for a seek and a scan is executed according to the execution plan. It shows two index scan's instead of one index scan and one index seek.

enter image description here

I would expect a index seek because in my case in dimension_instance only 10 of 15k records match entries in variable table.

Can anybody shed some light in my misunderstanding of how MS SQL indexes work.

Improve this question
2
  • > it seems as if the index isn't used at all. That can't be right. Obviously the index is used, because there are two index scans in the plan. It may not be doing the seek you want, but it's still used. What confuses me in the plan and question is that these are both PK indexes. I see no reference to the FK index in plan, which might reduce the cost of the join. But even your index hint was pushing the PK index, and not the FK. Perhaps if you added the name column as a non-indexed field to the FK index, so that the index covers the variable table, you might get different results. Commented Mar 10, 2014 at 16:56
  • You correctly edited the title of the question. I meant why it is a scan not a seek, will re-phase question accordingly as well. Commented Mar 10, 2014 at 17:19

3 Answers 3

Reset to default
1

The Query Execution Plan and the Query Optimizer estimate what is the better operation to do regarding the data inside the db and other variables: in your case maybe it thinks that the query will be less costly doing an index scan instead of a seek: this may be caused from low row numbers

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Also remember that data IO is always in extends of 64kb (8 pages of 8 kb). It amy simply not be efficient to make index lookups with this small amount of data.
1

it seems as if the index isn't used at all when I look at the execution plan.

Am I blind, are you blind or did you post the wrong execution plan?

The plan has two source tables and bot use a Clustered Index Scan. THat is100% usage of an index for source table access.

Now, why a scan and not a seek -well, because you dont have any limitations (where clause) and that may be the fastest way. If te machine assumes both tables must be fully read anyway, why doing a seek instead of a scan?

Improve this answer

1 Comment

You are right @TomTom I must re-phrase my question, why is there no index seek on the join, regarding your second point why must the second table be fully scanned. In my case in dimension_instance only 10 of 15k records match entries in variable ... I will note that down in the question as well
1

Can anybody shed some light in my misunderstanding of how MS SQL indexes work.

It's not the indexes you misunderstand, but the Hash Join. Hash Join just doesn't have a use for indexes on the join predicates (unlike nested loops join).

http://use-the-index-luke.com/sql/join/hash-join-partial-objects

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.