Sql-server – Why does sp_executesql use a different query plan

execution-planperformancequery-performancesql servert-sql

My C# application issues a query via Entity Framework which runs quickly in dev and test, but slowly in production.

I thought perhaps a bad query plan had been cached, so I tried DBCC freeproccache and DBCC dropcleanbuffers, but it still ran slowly. (I did this before and after each step of the investigation because I'm paranoid about bad plans being cached.)

I tried refactoring the query (taking it outside of sp_executesql) and it ran quickly.

Then I found an index missing from production, created it, and my app started to work quickly, while the original query in SSMS still ran slowly. It's not clear why the index would have made a difference – it's not helping with the join, and it doesn't cover all the columns required.

Question

What is influencing the choice of plan, such that wrapping it inside sp_executesql seems to be a factor? (And why does the app now perform OK while the same query in SSMS doesn't? It's usually the other way around due to bad cached plans.)

Original query:

exec sp_executesql N'SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[UnitId] AS [UnitId], 
[Limit1].[StreetNumber] AS [StreetNumber], 
[Limit1].[StreetAlpha] AS [StreetAlpha], 
[Limit1].[Name] AS [Name], 
[Limit1].[Name1] AS [Name1], 
[Limit1].[Direction] AS [Direction], 
[Limit1].[Name2] AS [Name2], 
[Limit1].[Name3] AS [Name3], 
[Limit1].[CorporateName] AS [CorporateName], 
[Limit1].[Surname] AS [Surname], 
[Limit1].[FirstNames] AS [FirstNames], 
[Limit1].[DIPID] AS [DIPID], 
[Limit1].[ValuationReference] AS [ValuationReference]
FROM ( SELECT DISTINCT TOP (51) 
    [Extent1].[DIPID] AS [DIPID], 
    [Extent1].[ValuationReference] AS [ValuationReference], 
    [Extent2].[CorporateName] AS [CorporateName], 
    [Extent2].[FirstNames] AS [FirstNames], 
    [Extent2].[Surname] AS [Surname], 
    [Extent3].[StreetNumber] AS [StreetNumber], 
    [Extent3].[UnitId] AS [UnitId], 
    [Extent3].[StreetAlpha] AS [StreetAlpha], 
    [Extent4].[Name] AS [Name], 
    [Extent4].[Direction] AS [Direction], 
    [Extent6].[Name] AS [Name1], 
    [Extent7].[Name] AS [Name2], 
    [Extent8].[Name] AS [Name3], 
    1 AS [C1]
    FROM        [Property].[Property] AS [Extent1]
    INNER JOIN [Property].[TitleEstate] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PropertyId]
    INNER JOIN [Property].[Address] AS [Extent3] ON [Extent1].[AddressId] = [Extent3].[Id]
    INNER JOIN [Reference].[Street] AS [Extent4] ON [Extent3].[StreetId] = [Extent4].[Id]
    LEFT OUTER JOIN [Reference].[StreetType] AS [Extent5] ON [Extent4].[StreetTypeId] = [Extent5].[Id]
    LEFT OUTER JOIN [Reference].[StreetType] AS [Extent6] ON [Extent4].[StreetTypeId] = [Extent6].[Id]
    INNER JOIN [Reference].[Suburb] AS [Extent7] ON [Extent3].[SuburbId] = [Extent7].[Id]
    INNER JOIN [Reference].[TerritorialAuthority] AS [Extent8] ON [Extent1].[TerritorialAuthorityId] = [Extent8].[Id]
    WHERE ((@p__linq__0 = 1) OR ((@p__linq__1 <> cast(1 as bit)) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM   [Property].[PropertyTitle] AS [Extent9]
        INNER JOIN [Title].[Title] AS [Extent10] ON [Extent10].[Id] = [Extent9].[TitleId]
        INNER JOIN [Reference].[LandDistrict] AS [Extent11] ON [Extent10].[LandDistrictId] = [Extent11].[Id]
        WHERE ([Extent1].[Id] = [Extent9].[PropertyId]) AND ([Extent11].[Code] = @p__linq__2)
    )))) AND ([Extent4].[Name] = @p__linq__3) AND ((0 = @p__linq__4) OR ((2 = @p__linq__5) AND (0 = ([Extent3].[StreetNumber] % 2))) OR ((1 = @p__linq__6) AND (1 = ([Extent3].[StreetNumber] % 2)))) AND (@p__linq__7 = N'''' OR [Extent5].[Name] = @p__linq__8) AND ((@p__linq__9 <> cast(1 as bit)) OR ((@p__linq__10 = 1) AND ([Extent3].[StreetNumber] >= @p__linq__11))) AND ((@p__linq__12 <> cast(1 as bit)) OR ((@p__linq__13 = 1) AND ([Extent3].[StreetNumber] <= @p__linq__14)))
)  AS [Limit1]',N'@p__linq__0 bit,@p__linq__1 bit,@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 int,@p__linq__5 int,@p__linq__6 int,@p__linq__7 nvarchar(4000),@p__linq__8 varchar(8000),@p__linq__9 bit,@p__linq__10 bit,@p__linq__11 int,@p__linq__12 bit,@p__linq__13 bit,@p__linq__14 int',@p__linq__0=0,@p__linq__1=0,@p__linq__2='WN',@p__linq__3='CHEYNE',@p__linq__4=0,@p__linq__5=0,@p__linq__6=0,@p__linq__7=N'',@p__linq__8='',@p__linq__9=1,@p__linq__10=1,@p__linq__11=4,@p__linq__12=1,@p__linq__13=1,@p__linq__14=12

Refactored query

declare @p__linq__0 bit,@p__linq__1 bit,@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 int,@p__linq__5 int,@p__linq__6 int,@p__linq__7 nvarchar(4000),@p__linq__8 varchar(8000),@p__linq__9 bit,@p__linq__10 bit,@p__linq__11 int,@p__linq__12 bit,@p__linq__13 bit,@p__linq__14 int
select  @p__linq__0=0,@p__linq__1=0,@p__linq__2='WN',@p__linq__3='CHEYNE',@p__linq__4=0,@p__linq__5=0,@p__linq__6=0,@p__linq__7=N'',@p__linq__8='',@p__linq__9=1,@p__linq__10=1,@p__linq__11=4,@p__linq__12=1,@p__linq__13=1,@p__linq__14=12

SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[UnitId] AS [UnitId], 
[Limit1].[StreetNumber] AS [StreetNumber], 
[Limit1].[StreetAlpha] AS [StreetAlpha], 
[Limit1].[Name] AS [Name], 
[Limit1].[Name1] AS [Name1], 
[Limit1].[Direction] AS [Direction], 
[Limit1].[Name2] AS [Name2], 
[Limit1].[Name3] AS [Name3], 
[Limit1].[CorporateName] AS [CorporateName], 
[Limit1].[Surname] AS [Surname], 
[Limit1].[FirstNames] AS [FirstNames], 
[Limit1].[DIPID] AS [DIPID], 
[Limit1].[ValuationReference] AS [ValuationReference]
FROM ( SELECT DISTINCT TOP (51) 
    [Extent1].[DIPID] AS [DIPID], 
    [Extent1].[ValuationReference] AS [ValuationReference], 
    [Extent2].[CorporateName] AS [CorporateName], 
    [Extent2].[FirstNames] AS [FirstNames], 
    [Extent2].[Surname] AS [Surname], 
    [Extent3].[StreetNumber] AS [StreetNumber], 
    [Extent3].[UnitId] AS [UnitId], 
    [Extent3].[StreetAlpha] AS [StreetAlpha], 
    [Extent4].[Name] AS [Name], 
    [Extent4].[Direction] AS [Direction], 
    [Extent6].[Name] AS [Name1], 
    [Extent7].[Name] AS [Name2], 
    [Extent8].[Name] AS [Name3], 
    1 AS [C1]
    FROM        [Property].[Property] AS [Extent1]
    INNER JOIN [Property].[TitleEstate] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PropertyId]
    INNER JOIN [Property].[Address] AS [Extent3] ON [Extent1].[AddressId] = [Extent3].[Id]
    INNER JOIN [Reference].[Street] AS [Extent4] ON [Extent3].[StreetId] = [Extent4].[Id]
    LEFT OUTER JOIN [Reference].[StreetType] AS [Extent5] ON [Extent4].[StreetTypeId] = [Extent5].[Id]
    LEFT OUTER JOIN [Reference].[StreetType] AS [Extent6] ON [Extent4].[StreetTypeId] = [Extent6].[Id]
    INNER JOIN [Reference].[Suburb] AS [Extent7] ON [Extent3].[SuburbId] = [Extent7].[Id]
    INNER JOIN [Reference].[TerritorialAuthority] AS [Extent8] ON [Extent1].[TerritorialAuthorityId] = [Extent8].[Id]
    WHERE ((@p__linq__0 = 1) OR ((@p__linq__1 <> cast(1 as bit)) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM   [Property].[PropertyTitle] AS [Extent9]
        INNER JOIN [Title].[Title] AS [Extent10] ON [Extent10].[Id] = [Extent9].[TitleId]
        INNER JOIN [Reference].[LandDistrict] AS [Extent11] ON [Extent10].[LandDistrictId] = [Extent11].[Id]
        WHERE ([Extent1].[Id] = [Extent9].[PropertyId]) AND ([Extent11].[Code] = @p__linq__2)
    )))) AND ([Extent4].[Name] = @p__linq__3) AND ((0 = @p__linq__4) OR ((2 = @p__linq__5) AND (0 = ([Extent3].[StreetNumber] % 2))) OR ((1 = @p__linq__6) AND (1 = ([Extent3].[StreetNumber] % 2)))) AND (@p__linq__7 = N'' OR [Extent5].[Name] = @p__linq__8) AND ((@p__linq__9 <> cast(1 as bit)) OR ((@p__linq__10 = 1) AND ([Extent3].[StreetNumber] >= @p__linq__11))) AND ((@p__linq__12 <> cast(1 as bit)) OR ((@p__linq__13 = 1) AND ([Extent3].[StreetNumber] <= @p__linq__14)))
)  AS [Limit1]

Missing index

CREATE NONCLUSTERED INDEX [IX_PropertyTitleEstate_Surname_FirstNames] ON [Property].[TitleEstate]
(
    [Surname] ASC,
    [FirstNames] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Source LINQ

var results = (from p in _db.Properties
               join e in _db.TitleEstates on p.Id equals e.Property.Id
               where (allLandDistricts || (!allLandDistricts && p.Titles.Any(t => t.LandDistrict.Code == landDistrict)))
               && p.Address.Street.Name == streetName
               && (accessType == SearchAccessType.All || (accessType == SearchAccessType.Even && p.Address.StreetNumber % 2 == 0) || (accessType == SearchAccessType.Odd && p.Address.StreetNumber % 2 == 1))
               && (streetType == "" || p.Address.Street.StreetType.Name == streetType)
               && (!hasStNumberFrom || (hasStNumberFrom && p.Address.StreetNumber >= stNumberFrom))
               && (!hasStNumberTo || (hasStNumberTo && p.Address.StreetNumber <= stNumberTo))
               select new PropertyResult
               {
                   UnitId = p.Address.UnitId,
                   StreetNumber = p.Address.StreetNumber,
                   StreetAlpha = p.Address.StreetAlpha,
                   StreetName = p.Address.Street.Name,
                   StreetType = p.Address.Street.StreetType.Name,
                   StreetDirection = p.Address.Street.Direction,
                   Suburb = p.Address.Suburb.Name,
                   TerritorialAuthority = p.TerritorialAuthority.Name,
                   CorporateName = e.CorporateName,
                   Surname = e.Surname,
                   FirstNames = e.FirstNames,
                   ValocityId = p.DIPID,
                   ValRef = p.ValuationReference
               }).OrderBy(p => p.UnitId)
    .ThenBy(p => p.StreetNumber)
    .ThenBy(p => p.StreetAlpha)
    .ThenBy(p => p.StreetName)
    .ThenBy(p => p.StreetType)
    .ThenBy(p => p.CorporateName)
    .ThenBy(p => p.Surname)
    .ThenBy(p => p.FirstNames).Distinct()
    .Take(rowLimit).ToList();

Best Answer

_{Community wiki answer:}

Parameters and local variables are different beasts.

The plan with parameters is generated (if not already cached) using row count estimates gleaned from the actual values supplied and statistic histograms.

The plan with local variables is generated based on unknown values so the average overall density (all_density) is used to estimate row counts.

The plans may differ due to different row count estimates.

Related: Understanding Performance Mysteries by Erland Sommarskog

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested. The graphical execution plan seems to indicate this is the case -- TOP is the "last" step. But it appears there is more going on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes. An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close(). As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator). This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards. This row-at-a-time model is essential in forming a sound intuition for query execution.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives. The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets. The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed. The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree. The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal. This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans. Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans. More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it. Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best. Which option is appropriate in your case depends on the details of course. The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.

Sql-server – Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best. Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)

Best Answer

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

Sql-server – Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

Related Question