2

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();
0
4

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

0
3

You can start troubleshooting your query first by checking if your Entity framework request is reusing the plan. Creating a new plan for every query can be quite a time and resource consuming and you dont need that

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st].[text] ,
[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
where [st].[dbid] = DB_ID()

This will give you a list of all cached plans within the database, revealing the text of the query and the query plan, which can help you further troubleshoot the query.

Take snapshot of a result set and execute the query from the application to see if you`re getting a new plan for each execution. If you do then you should consider wrapping it up in a stored procedure, and calling a stored procedure from the application.

If you decide to use a stored procedure instead, it will help not only with plan reusability but also with the plan estimations. It will create a better plan if you are searching for more selective values. Also be aware of parameter sniffing as it can also hurt performance by virtue of selecting an incorrect plan.

Use following query to compare them and see how much local variables can affect your logical reads and cpu time.

select TOP 30 last_execution_time,text,p.query_plan,execution_count,last_worker_time,last_logical_reads,last_elapsed_time from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as text
cross apply sys.dm_exec_query_plan(plan_handle) as p
ORDER BY last_execution_time DESC
1
  • Very comprehensive answer. If going the sproc route, it's also worth mentioning the possibility of parameters sniffing. Which can cripple performance, and (at least in my experiences) tends to happen when going from Dev to Production. – pim May 6 '18 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.