Incorrect execution plan

Question

I'm running a select query. On certain days it's using an incorrect execution plan. Other days it's using the correct execution plan. I have verified with (nolock) option, index stats, blocking, CPU, and memory load; everything is normal. Are there any other ways to check why it is using the wrong execution plan?

The query:

DECLARE @ProjectName as nvarchar
DECLARE @vehicleName as nvarchar
DECLARE @fromDate as datetime2
DECLARE @toDate as datetime2

select 
    d.DDSEventId, d.PrjName, d.VehicleName, d.StartTime, d.EndTime, 
    e.Name, s.Value, es.Name, s.DDSSignalId, e.ProcessId, e.EventId, 
    d.VehiclePos, e.DefaultDescription, es.DefaultDescription, 
    d.CarName, d.ExclusionState
from
    DDSEvents d
inner join 
    Events e on d.DiagnosticConfigurationId = e.DiagnosticConfigurationId 
             and d.EventId = e.EventId 
             and d.ProcessId = e.ProcessId
left join 
    DDSSignals s on d.DDSEventId = s.DDSEventId 
                 and s.StartTime = d.StartTime
left join 
    EnvironmentSignals es on s.EnvironmentSignalId = es.EnvironmentSignalId
where 
    d.PrjName = @projectName and
    d.VehicleName = @vehicleName and
    d.StartTime >= @fromDate and
    d.StartTime < @toDate

Correct plan

Wrong plan

I have declared the same variables for both execution plans.

Answer 1

An execution plan could change for the same parameters for numerous reasons. Different set options such as ANSI_NULLS etc., statistics updates or data variance between executions.

As you are using SQL Server 2008 R2, you can control the plan using query hints. However, these should be avoided where possible as they are hard-coded and will remain in place when data volumes change in the future. Far better to let the optimiser choose the best plan after you maintain statistics and indexes appropriately.

If you really need to fix a query plan, a plan guide is another option as you don't have to modify any code.

If you ever upgrade to SQL Server 2016, the Query Store also allows you to fix the plan for a query with a single click.