Sql-Server-2012

如何擺脫聚集索引掃描？

  • June 1, 2014

我有一個由 C#/Linq-To-Entities 程式碼生成的查詢。與這種查詢一樣，它看起來非常醜陋，我懷疑將它包含在此處會有所幫助。但是我通過查詢分析器執行它，發現 50.5% 的處理時間發生在“嵌套循環（內部連接）”步驟中，該步驟正在加入聚集索引搜尋a2_AssessmentQuestion（0.3%）和聚集索引掃描同一張表（13.1%）。

我承認我並不真正理解這個“嵌套循環連接”在做什麼，但是為什麼查詢會在執行索引搜尋後立即對錶進行掃描，而它正在查找的欄位可以直接通過另一個索引獲得究竟是必填欄位？是的，我已經執行update statistics a2_AssessmentQuestion了，但沒有效果。

不是了解查詢計劃的專家，如果您能促使我回答相關問題，我將不勝感激；我可能只是不夠了解，無法為您提供足夠有用的資訊。

謝謝！

在此處輸入圖像描述

編輯： FWIW，這是查詢：

SELECT 
[Project6].[ID] AS [ID], 
[Project6].[ID2] AS [ID1], 
[Project6].[ID1] AS [ID2], 
[Project6].[ID3] AS [ID3], 
[Project6].[ID5] AS [ID4], 
[Project6].[ID7] AS [ID5], 
[Project6].[ID6] AS [ID6], 
[Project6].[ID8] AS [ID7], 
[Project6].[ID9] AS [ID8], 
[Project6].[ID11] AS [ID9], 
[Project6].[ID10] AS [ID10], 
[Project6].[ID12] AS [ID11], 
[Project6].[ID13] AS [ID12], 
[Project6].[Number] AS [Number], 
[Project6].[ScholarAssessmentID] AS [ScholarAssessmentID], 
[Project6].[C1] AS [C1], 
[Project6].[GradingStyle] AS [GradingStyle], 
[Project6].[PartID] AS [PartID], 
[Project6].[C2] AS [C2], 
[Project6].[ID4] AS [ID13], 
[Project6].[Description] AS [Description], 
[Project6].[Version] AS [Version], 
[Project6].[DifficultyLevel] AS [DifficultyLevel], 
[Project6].[AssessmentQuestionID] AS [AssessmentQuestionID], 
[Project6].[GradingDueDate] AS [GradingDueDate], 
[Project6].[C3] AS [C3], 
[Project6].[AssessmentFormat] AS [AssessmentFormat], 
[Project6].[C4] AS [C4], 
[Project6].[Structure] AS [Structure], 
[Project6].[Title] AS [Title], 
[Project6].[C5] AS [C5], 
[Project6].[AssessmentFormat1] AS [AssessmentFormat1], 
[Project6].[C6] AS [C6], 
[Project6].[Label] AS [Label]
FROM ( SELECT 
   [Extent1].[ID] AS [ID], 
   [Extent1].[ScholarAssessmentID] AS [ScholarAssessmentID], 
   [Extent1].[AssessmentQuestionID] AS [AssessmentQuestionID], 
   [Join1].[ID1] AS [ID1], 
   [Join1].[ID2] AS [ID2], 
   [Extent4].[ID] AS [ID3], 
   [Extent4].[PartID] AS [PartID], 
   [Extent4].[Number] AS [Number], 
   [Extent4].[DifficultyLevel] AS [DifficultyLevel], 
   [Extent4].[Structure] AS [Structure], 
   [Extent4].[GradingStyle] AS [GradingStyle], 
   [Extent5].[ID] AS [ID4], 
   [Extent5].[Description] AS [Description], 
   [Extent5].[Version] AS [Version], 
   [Extent6].[ID] AS [ID5], 
   [Extent6].[GradingDueDate] AS [GradingDueDate], 
   [Extent6].[Title] AS [Title], 
   [Join6].[ID3] AS [ID6], 
   [Join6].[ID4] AS [ID7], 
   [Extent9].[ID] AS [ID8], 
   [Extent10].[ID] AS [ID9], 
   [Extent10].[AssessmentFormat] AS [AssessmentFormat], 
   [Join10].[ID5] AS [ID10], 
   [Join10].[ID6] AS [ID11], 
   [Extent13].[ID] AS [ID12], 
   [Extent14].[ID] AS [ID13], 
   [Extent14].[AssessmentFormat] AS [AssessmentFormat1], 
    CAST( [Extent4].[Points] AS bigint) AS [C1], 
    CAST( [Extent1].[PointsAwarded] AS decimal(19,0)) AS [C2], 
    CAST( [Extent6].[DueDate] AS datetime2) AS [C3], 
    CAST( [Extent4].[Number] AS bigint) AS [C4], 
   [Join14].[Label] AS [Label], 
   CASE WHEN ([Extent4].[RubricWeight] IS NOT NULL) THEN  CAST( [Extent4].[RubricWeight] AS int) ELSE 0 END AS [C5], 
   CASE WHEN ([Join14].[ScholarAssessmentAnswerID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C6]
   FROM            [dbo].[a2_ScholarAssessmentAnswer] AS [Extent1]
   INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[AssessmentID] AS [AssessmentID], [Extent3].[ID] AS [ID2]
       FROM  [dbo].[a2_ScholarAssessment] AS [Extent2]
       LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent3] ON [Extent2].[ID] = [Extent3].[ID] ) AS [Join1] ON [Extent1].[ScholarAssessmentID] = [Join1].[ID1]
   INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent4] ON [Extent1].[AssessmentQuestionID] = [Extent4].[ID]
   LEFT OUTER JOIN [dbo].[a2_AssessmentQuestionType] AS [Extent5] ON [Extent4].[QuestionTypeID] = [Extent5].[ID]
   INNER JOIN [dbo].[a2_AssessmentPart] AS [Extent6] ON [Extent4].[PartID] = [Extent6].[ID]
   INNER JOIN  (SELECT [Extent7].[ID] AS [ID3], [Extent7].[AssessmentID] AS [AssessmentID], [Extent8].[ID] AS [ID4]
       FROM  [dbo].[a2_ScholarAssessment] AS [Extent7]
       LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent8] ON [Extent7].[ID] = [Extent8].[ID] ) AS [Join6] ON [Extent1].[ScholarAssessmentID] = [Join6].[ID3]
   INNER JOIN [dbo].[a2_Assessment] AS [Extent9] ON [Join6].[AssessmentID] = [Extent9].[ID]
   LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent10] ON [Extent9].[AssessmentTypeID] = [Extent10].[ID]
   INNER JOIN  (SELECT [Extent11].[ID] AS [ID5], [Extent11].[AssessmentID] AS [AssessmentID], [Extent12].[ID] AS [ID6]
       FROM  [dbo].[a2_ScholarAssessment] AS [Extent11]
       LEFT OUTER JOIN [dbo].[a2_ScholarFNPAssessment] AS [Extent12] ON [Extent11].[ID] = [Extent12].[ID] ) AS [Join10] ON [Extent1].[ScholarAssessmentID] = [Join10].[ID5]
   INNER JOIN [dbo].[a2_Assessment] AS [Extent13] ON [Join10].[AssessmentID] = [Extent13].[ID]
   LEFT OUTER JOIN [dbo].[a2_AssessmentType] AS [Extent14] ON [Extent13].[AssessmentTypeID] = [Extent14].[ID]
   LEFT OUTER JOIN  (SELECT [Extent15].[ScholarAssessmentAnswerID] AS [ScholarAssessmentAnswerID], [Extent16].[Label] AS [Label]
       FROM  [dbo].[a2_ScholarAssessmentAnswerSelection] AS [Extent15]
       INNER JOIN [dbo].[a2_AssessmentAnswers] AS [Extent16] ON [Extent15].[AssessmentAnswerID] = [Extent16].[ID] ) AS [Join14] ON [Extent1].[ID] = [Join14].[ScholarAssessmentAnswerID]
   WHERE (1538 = [Join1].[AssessmentID]) AND ( NOT EXISTS (SELECT 
       1 AS [C1]
       FROM ( SELECT 
           [Extent18].[ID] AS [ID]
           FROM  [dbo].[a2_AssessmentQuestion] AS [Extent17]
           INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18] ON [Extent17].[PartID] = [Extent18].[PartID]
           WHERE [Extent1].[AssessmentQuestionID] = [Extent17].[ID]
       )  AS [Project1]
       WHERE ( NOT EXISTS (SELECT 
           1 AS [C1]
           FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent19]
           WHERE ([Extent1].[ScholarAssessmentID] = [Extent19].[ScholarAssessmentID]) AND ([Extent19].[AssessmentQuestionID] = [Project1].[ID])
       )) OR (CASE WHEN ( EXISTS (SELECT 
           1 AS [C1]
           FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent20]
           WHERE ([Extent1].[ScholarAssessmentID] = [Extent20].[ScholarAssessmentID]) AND ([Extent20].[AssessmentQuestionID] = [Project1].[ID])
       )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
           1 AS [C1]
           FROM [dbo].[a2_ScholarAssessmentAnswer] AS [Extent21]
           WHERE ([Extent1].[ScholarAssessmentID] = [Extent21].[ScholarAssessmentID]) AND ([Extent21].[AssessmentQuestionID] = [Project1].[ID])
       )) THEN cast(0 as bit) END IS NULL)
   ))
)  AS [Project6]
ORDER BY [Project6].[ID] ASC, [Project6].[ID2] ASC, [Project6].[ID1] ASC, [Project6].[ID3] ASC, [Project6].[ID5] ASC, [Project6].[ID7] ASC, [Project6].[ID6] ASC, [Project6].[ID8] ASC, [Project6].[ID9] ASC, [Project6].[ID11] ASC, [Project6].[ID10] ASC, [Project6].[ID12] ASC, [Project6].[ID13] ASC, [Project6].[ID4] ASC, [Project6].[C6] ASC

我試圖包括執行計劃，但這讓我超過了 30,000 個字元的限制。

瘋狂的是，我提供了 AssessmentID = 1538，這是查詢中最有用的限制資訊，但執行計劃幾乎忽略了這個事實，並且掃描了幾乎所有其他連接表，僅按 AssessmentID 過濾作為事後的想法…

嵌套循環發生在這部分，您將在其中加入a2_AssessmentQuestion自身：

SELECT
  [Extent18].[ID] AS [ID]
FROM
  [dbo].[a2_AssessmentQuestion] AS [Extent17]
  INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18]
     ON [Extent17].[PartID] = [Extent18].[PartID]
WHERE
  [Extent1].[AssessmentQuestionID] = [Extent17].[ID]

從您發布的整個查詢中可以看出，此部分不應用 where 條件。

查看嵌套循環和聚集索引掃描的謂詞和輸出，我建議您嘗試在a2_AssessmentQuestion.PartID.

執行計劃中的嵌套循環詳細資訊 執行計劃中的聚集索引掃描詳細資訊

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

相關問答

Sql-Server

重新索引是否更新統計資訊？

  • March 17, 2021
檢視問答
Sql-Server-2012

為什麼 SQL Server 不使用它推薦的索引？

  • May 24, 2020
檢視問答
Sql-Server

得到掃描雖然我期待尋找

  • May 8, 2020
檢視問答
Index

如果 DMVS 中沒有統計資訊，可以刪除 FK 上的索引

  • May 8, 2020
檢視問答
Sql-Server

執行計劃不使用索引，它使用表掃描

  • May 7, 2020
檢視問答
Mysql

為什麼我們在 MySQL 索引中不能有多個不等式條件？

  • September 30, 2019
檢視問答