Sql-Server

為什麼 sp_executesql 使用不同的查詢計劃？

  • May 6, 2018

我的 C# 應用程序通過 Entity Framework 發出查詢，該查詢在開發和測試中執行迅速，但在生產中執行緩慢。

我認為可能記憶體了一個錯誤的查詢計劃，所以我嘗試了DBCC freeproccacheand DBCC dropcleanbuffers，但它仍然執行緩慢。（我在調查的每個步驟之前和之後都這樣做了，因為我對記憶體不好的計劃感到偏執。）

我嘗試重構查詢（將其置於外部sp_executesql）並且它執行得很快。

然後我發現生產中缺少一個索引，創建它，我的應用程序開始快速執行，而 SSMS 中的原始查詢仍然執行緩慢。目前尚不清楚為什麼索引會有所作為 - 它對連接沒有幫助，並且它沒有涵蓋所需的所有列。

題

是什麼影響了計劃的選擇，以至於將其包裹在裡面sp_executesql似乎是一個因素？（為什麼應用程序現在可以執行，而 SSMS 中的相同查詢卻沒有？由於記憶體計劃錯誤，通常情況相反。）

原始查詢：

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

重構查詢

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]

缺少索引

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)

源 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();

社區維基回答：

參數和局部變數是不同的野獸。

使用從提供的實際值和統計直方圖收集的行數估計值生成帶有參數的計劃（如果尚未記憶體）。

具有局部變數的計劃是根據未知值生成的，因此平均總密度 ( all_density) 用於估計行數。

由於行數估計不同，計劃可能會有所不同。

相關：理解Erland Sommarskog 的性能奧秘

您可以首先通過檢查您的實體框架請求是否正在重用計劃來開始對查詢進行故障排除。為每個查詢創建一個新計劃可能會耗費大量時間和資源，而您不需要這樣做

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()

這將為您提供數據庫中所有記憶體計劃的列表，顯示查詢文本和查詢計劃，這可以幫助您進一步排除查詢故障。

拍攝結果集的快照並從應用程序執行查詢，以查看您是否為每次執行獲取新計劃。如果你這樣做了，那麼你應該考慮將它包裝在一個儲存過程中，並從應用程序中呼叫一個儲存過程。

如果您決定改用儲存過程，它不僅有助於計劃的可重用性，而且有助於計劃估計。如果您正在尋找更具選擇性的值，它將創建一個更好的計劃。還要注意參數嗅探，因為它也會因選擇不正確的計劃而損害性能。

使用以下查詢來比較它們，看看有多少局部變數會影響您的邏輯讀取和 CPU 時間。

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

引用自：https://dba.stackexchange.com/questions/177326

相關問答

Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

非聚集主鍵和覆蓋索引在性能方面的比較

  • December 9, 2020
檢視問答
Sql-Server

生產中的查詢慢、執行計劃錯誤或索引錯誤？

  • August 19, 2019
檢視問答
Sql-Server

SQL Server 昂貴的嵌套循環連接和惰性表假離線

  • August 2, 2019
檢視問答
Sql-Server

如何更快地獲得最近行的執行總數？

  • April 4, 2019
檢視問答
Sql-Server

優化表值函式 SQL Server

  • January 2, 2019
檢視問答