CODEWITHSUNDEEP
sqlsql-serverperformanceindexing
Jakub Szułakiewicz
Jakub Szułakiewicz

Reputation: 840

Full table scan over indexed columns?

I have the following query:

select 
    Table1.Id,
    Table1.SomeColumn1,
    Table1.SomeColumn2,
    Table1.SomeColumn3
    Table2.Id,
    Table2.SomeColumn,
from   
    Table1 
inner join 
    Table2 on Table1.Table2_Id = Table2.Id
inner join 
    Table3 on Table3.Id = Table2.Table3_Id
where  
    Table3.Some_Column = 181715

Despite that, the query's performance is terrible.

Query execution plan looks following: https://snipboard.io/Xd67ru.jpg

As we can see, the database engine is making full table scan over Table1, which is completely unnecessary (as all joins are over indexed columns).

What is wrong? How to improve it?

Upvotes: 2

Views: 801

Answers (1)

marc_s
marc_s

Reputation: 754508

Just a guess - but what I'd try first would be to make those indexes covering, e.g. adding some additional columns as included columns to those so that the query can be satisfied from the nonclustered index - instead of having to go back to the base table with an expensive "Key Lookup" to grab more columns.

Try to update your indexes to:

CREATE NONCLUSTERED INDEX IX_Table1
ON dbo.Table1(Table2_Id)    --- create index on foreign key column
INCLUDE (SomeColum1, SomeColumn2, SomeColumn3);

CREATE NONCLUSTERED INDEX IX_Table2
ON dbo.Table2(Table3_Id)
INCLUDE (SomeColumn);

With these "covering" indexes, the whole query (all the columns you need as results) as present in the leaf level of those indexes - so just by using index lookups, the whole query can be "satisfied". This should avoid expensive key lookups into the base table, and probably will also cause the "full table scan" to go away.

Give it a try!

Upvotes: 4

Related Questions