SQL Server – Troubleshooting Incorrect Execution Plans

execution-plansql serversql-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server using incorrect plan

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS. Very likely, your connection from SSMS has different settings from what your .Net application. This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.

SQL Server Execution Plan – SHOWPLAN vs ‘Include Execution Plan’ Warnings for the Same Query

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

_{* Disclaimer: I work for SQL Sentry.}