SQL Server execution plan is using PK instead of index

execution-planindex

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?

Best Answer

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
Related Question