為什麼此查詢中的並行性不好？

我在記憶體的執行計劃中尋找缺失的索引，然後我發現了 Jonathan Kehayias 的一篇非常好的文章：

在計劃記憶體中查找鍵查找

從那裡我借了以下腳本：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--IF OBJECT_ID('TEMPDB..#MissingIndexInfo') IS NOT NULL
--   DROP TABLE #MissingIndexInfo;

WITH XMLNAMESPACES
  (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
   n.query('.'),
   i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp,
   i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName,
   i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName,
   i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName,
   i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName,
   i.query('.'),
   STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
      FROM i.nodes('./OutputList/ColumnReference') AS t(cg)
      FOR  XML PATH('')),1,2,'') AS output_columns,
   STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
      FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
      FOR  XML PATH('')),1,2,'') AS seek_columns,
   i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') as Predicate,
   cp.usecounts,
   query_plan
FROM (  SELECT plan_handle, query_plan
       FROM (  SELECT DISTINCT plan_handle
               FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
       OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
     ) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp 
   ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n)
CROSS APPLY n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') as s(i)
OPTION(RECOMPILE, MAXDOP 1);

我注意到了，MAXDOP 1但我不確定它如何提高查詢的性能。

一般不會。它之所以存在，是因為 XML 查詢是魔鬼自己製造的痛苦，而 Jonathan 正試圖不在您的伺服器上造成觀察者成本。

作為替代方案，sp_BlitzCache只會警告您昂貴的密鑰查找。

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