SQL Server execution plan is using PK instead of index

Question

The following query is taking 5 seconds to return 9692 rows:

SELECT 
 Project.CalculateSequencialProject,
 Project.CreateDate,
 Project.OpeningDate,
 Project.ForecastClosingDate,
 Project.RealClosingDate,
 Project.ProjectStatus,
 Unit.Name AS UnitName,
 UTE.Name AS UTEName,
 Process.Name AS Process 
FROM 
 Project
 LEFT JOIN Unit ON Project.Unit_Id = Unit.Id AND Project.Company_Id = Unit.Company_Id
 LEFT JOIN UTE ON Project.UTE_Id = UTE.Id
 LEFT JOIN Process ON Project.Process_Id = Process.Id
WHERE 
 Project.Company_Id = '????????????????'

The execution plan is like this: ExecutionPlan

And the most "expensive" part of it is detailed here: Detail

As you may se in the tooltip image, the Clustered Index Scan with 75% of cost is using the table primary key instead of one of the following indexes.

CREATE NONCLUSTERED INDEX [IX_Company_Id] ON [dbo].[Project]
(
 [Company_Id] ASC
)
CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
 [ProjectType_Id] ASC,
 [ProjectStatus] ASC,
 [Unit_Id] ASC,
 [Id] ASC,
 [ProjectDirector_Id] ASC,
 [UTE_Id] ASC,
 [Company_Id] ASC,
 [LostType_Id] ASC,
 [Pillar_Id] ASC,
 [ProjectPhase_Id] ASC
)
INCLUDE ( [CalculateSequencialProject],
 [DescriptionTittle],
 [OpeningDate],
 [ForecastClosingDate],
 [RealClosingDate])

Why?

• a1ex07: It seems to me that index on Company_Id is not selective enough, and optimizer decides that full table scan (clustered index scan) is cheaper than index range scan + lookup.
• juliano.net: What do you suggest me to change so I can improve this query? The Project table has only 27k rows, I don't understand why such query is taking 5 seconds to run.
• Jeffrey Van Laethem: What columns exactly does the PK consist of?
• juliano.net: Just the [Id] column.

Answer 1

None of your indexes are good for this query

• IX_Company_Id works only for the WHERE
• IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE

This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
 [Company_Id] , --can swap these 2 to see what happens if no index on Unit
 [Unit_Id] ,
 [UTE_Id] ,
 ProcessID
)
INCLUDE
 CalculateSequencialProject,
 CreateDate,
 OpeningDate,
 ForecastClosingDate,
 RealClosingDate,
 ProjectStatus

• juliano.net: If I drop all my indexes and create a new one like the one you suggested, and I have another query in my application that doesn't need Company_id, but needs Unit_Id, will it use this index?
• gbn: Probably not, but I did not say drop any existing indexes. And any query with Unit_Id won't use the current indexes either for the same reasons I gave
• juliano.net: I was saying to drop existing indexes because they seem to be duplicated or even not used at all.