當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

TSQL查询内幕::(2.3)查询计划与更新计划

發布時間：2025/3/14 数据库 15 豆豆

生活随笔收集整理的這篇文章主要介紹了 TSQL查询内幕::(2.3)查询计划与更新计划小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

顯示計劃(Showplan)是表示由查詢優化器生成的文本、圖形或XML格式的查詢計劃的術語。他包含了有關SQL Server如何處理查詢的信息，對查詢計劃中的每個表，顯示計劃可以告訴你是否使用了索引，或者是否有必要執行表掃描，以及不同操作的執行順序。

在本系列隨筆的2.1中對一個顯示計劃做了初步的分析。

SQL Server2005可以生成三種不同格式的顯示計劃：圖形、文本和XML。

在計劃內容方面，SQL Server可以生成只包含運算符的計劃，包含估計成本的計劃，以及包含運行時信息的計劃。下表列出了生成不同格式計劃的命令：

內容	格式
內容	文本	XML	圖形
運算符	SET SHOWPLAN_TEXT ON	N/A	N/A
運算符和估計成本	SET SHOWPLAN_ALL ON	SET SHOWPLAN_XML ON	在企業管理器中“顯示估計的執行計劃”
運行時信息	SET STATISTICS PROFILE ON	SET STATISTICS XML ON	在企業管理器中“包含實際的執行計劃”

首先看一個簡單的查詢：

SET NOCOUNT ON;

USE Northwind;

SET SHOWPLAN_TEXT ON;

SELECT ProductName, Products.ProductID

FROM dbo.[Order Details]

?JOIN dbo.Products

??? ON [Order Details].ProductID = Products.ProductID

WHERE Products.UnitPrice > 100;

SET SHOWPLAN_TEXT OFF;

運行結果：

StmtText

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT ProductName, Products.ProductID

FROM dbo.[Order Details]

?JOIN dbo.Products

??? ON [Order Details].ProductID = Products.ProductID

WHERE Products.UnitPrice > 100;

StmtText

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

?|--Nested Loops(Inner Join, OUTER REFERENCES:([Northwind].[dbo].[Products].[ProductID]))

?????? |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Products].[PK_Products]), WHERE:([Northwind].[dbo].[Products].[UnitPrice]>($100.0000)))

?????? |--Index Seek(OBJECT:([Northwind].[dbo].[Order Details].[ProductID]), SEEK:([Northwind].[dbo].[Order Details].[ProductID]=[Northwind].[dbo].[Products].[ProductID]) ORDERED FORWARD)

輸出結果表明：該查詢由三個運算符組成：Nested Loops、Clustered Index Scan、Index Seek。

首先看第一句：

|--Nested Loops(Inner Join, OUTER REFERENCES:([Northwind].[dbo].[Products].[ProductID]))

Nested Loops對兩個表進行內部聯接，且外部表為Products表。

然后是：

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Products].[PK_Products]), WHERE:([Northwind].[dbo].[Products].[UnitPrice]>($100.0000)))

SQL Server使用Clustered Index Scan訪問物理數據，這里掃描聚集索引相當于描述整個表。

最后是：

|--Index Seek(OBJECT:([Northwind].[dbo].[Order Details].[ProductID]), SEEK:([Northwind].[dbo].[Order Details].[ProductID]=[Northwind].[dbo].[Products].[ProductID]) ORDERED FORWARD)

SQL Server使用Index Seek訪問索引行，其中的Object顯示了索引的完整名稱。Seek是查找謂詞，這里是要根據外部表的ProductID來進行索引查找。

當執行計劃時，數據的傳遞通常是從右到左，從上到下的。縮進多的運算符生成行供縮進少的運算符使用。在這里，Clustered Index Scan運算符和Index Seek運算符是縮進最多的，且Clustered Index Scan在Index Seek的上面，所以先運算Clustered Index Scan，然后是Index Seek，最后是Nested Loops。

運行示例還可以注意到，當使用了SET SHOWPLAN_TEXT ON后，會阻止執行查詢。

XML格式的顯示計劃

有兩種格式的顯示計劃。一個是SET SHOWPLAN_XML ON，他將包括估計的執行計劃。另一個是SET STATISTICS XML ON，他包含運行時的信息。

生成XML格式的顯示計劃可以通過以下方法：

1. 上面寫的這兩個指令，其中SET SHOWPLAN_XML ON在編譯批處理時生成，它為整個批處理生成一個XML文檔；而SET STATISTICS XML ON在運行時產生輸出，他為批處理中的每個語句生成單獨的XML文檔。

2. 使用企業管理器的“顯示圖形化顯示計劃”。

3. 使用SQL Server Profiler。

名為Showplanxml.xsd的XML Schema描述了包括編譯時估計、運行時的XML顯示計劃。在運行時，XML顯示計劃提供了一些額外的信息。這個文件在安裝完SQL Server 2005后，被放置在Microsoft SQL Server"90"tools"Binn"schemas"sqlserver"2004"07"showplan目錄下。

XML格式的顯示計劃的內容最詳細，包含了計劃大小(CachePlanSize屬性)，和優化該計劃是用到的參數值(ParameterList元素)，而且只有運行時XML顯示計劃才包含并行計劃中不同線程所處理的行數(RunTimeCountersPerThread元素的ActualRows屬性)，以及執行查詢時的實際平行度(DegreeOfParallelism屬性)。

圖形化的顯示計劃

在企業管理器中，有“顯示估計的執行計劃”和“包括實際的執行計劃”兩種圖形化的顯示方法。

顯示估計的執行計劃：點選后，在結果窗口會立刻顯示圖形化執行計劃。

包含實際的執行計劃：點選后，不會立刻顯示計劃，而是在點擊執行后，將實際的計劃結果顯示出來。

無論使用上面哪種方式圖形顯示計劃，都會顯示下面的圖形：

但可以想到他們之間存在的區別，當查看運算符的詳細信息時：

因為“包含實際的執行計劃”，所以會給出實際運行時的一些信息。

右鍵->計劃另存為，還可以將顯示計劃保存為XML顯示計劃。擴展名為sqlplan。

顯示計劃中的運行時信息

SET STATISTICS XML ON|OFF

XML顯示計劃包含兩種運行時信息：每個SQL語句的信息和每個線程的信息。如果語句有參數，它的計劃將包含ParameterRuntimeValue屬性，表示該語句被執行時每個參數的值。它可能不同于編譯該語句時用到的值(ParameterCompiledValue屬性)，但只有優化器在優化并知道該參數值時，該屬性才會出現在計劃中，且只與傳遞到存儲過程的參數有關。

DegreeOfParallelism屬性表示此次執行的實際平行度(或DOP，它是單個查詢的并發線程數)。它可能與編譯時計算的值不同，在編譯期間，查詢優化器假設為當時的工作負荷為CPU的一半。在執行時，DOP的值會根據執行時開始的工作負荷被調整。如果執行并行計劃時DOP=1，當創建執行上下文時，SQL Server將從查詢計劃中移除Exchange運算符。MemoryGrant屬性表示以KB為單位的用于執行該查詢的實際內存。SQL Server使用這些內存為哈希聯接(hash Join)生成哈希表或在內存中執行排序。

RunTimeCountersPerThread元素包含5個屬性，每個線程都有相應的值：ActualRebinds、ActualRewinds、ActualRows、ActualEndofScans、ActualExecutions。ActualExecutions值告訴我們該運算符在每個線程中被初始化的次數。如果運算符是一個掃描運算符，ActualEndofScans表示掃描到達數據集結尾的次數。所以用ActualExecutions-ActualEndofScans就可以得到運算符沒有掃描的次數。如：如果SELECT中使用TOP限定了返回的行數，則輸出集合將在掃描到達表的結尾之前被收集。

SET STATISTICS PROFILE ON

這個指令返回的計劃與SET SHOWPLAN_ALL ON相比有兩個區別。他在輸出中包含了另外的兩列：Rows和Executes。

Rows ：是所有線程的RunTimeCountersPerThread元素RowCount屬性的合計，他告訴我們每個運算符實際返回的行數。

Executes：是該元素中ActualExecutions屬性的合計，他告訴我們SQL Server為處理一行或多行而初始化該運算符的次數。

當檢查某個查詢計劃時，可以找到查詢優化器的估計行數與實際行數之間的最大差異。EstimateRows列是每次執行所估計的輸出行數，而Rows是運算符所有執行返回的累積行數。因此我們可以先把EstimateRows乘以EstimateExcutions，在把它與SET STATISTICS PROFILE輸出的Roes列中返回的實際總行數做比較。

用SQL跟蹤捕獲顯示計劃

使用跟蹤來捕獲顯示計劃是非常精確的，這樣可以避免在企業管理器中觀察到的計劃和在應用程序執行起來使用的實際計劃之間產生的差異。最常見的，如：用不同的參數調用同一個存儲過程、統計信息被自動更新、在編譯和運行之間的可用資源(CPU或內存)發生變化。

使用跟蹤進行監視非常消耗資源，監視的事件越多，影響越嚴重。

下表顯示了9類性能事件：

跟蹤事件類	編譯或運行	是否包含運行時信息	是否包含XML顯示計劃	是否為SQL Server 2000生成跟蹤
Showplan ALL	運行	否	否	是
Showplan All for Query Compile	編譯	否	否	否
Showplan Statistics Profile	運行	是	否	是
Showplan Text	運行	否	否	是
Showplan Text(未編碼)	運行	否	否	是
Showplan XML	運行	否	是	否
Showplan XML for Query Compile	編譯	否	是	否
Showplan XML Statistics Profile	運行	是	是	否
Performance Statistics	編譯和運行	是	是	否

如果在開發或調試中，應該使用Showplan XML Statistics Profile事件。它生成所有的查詢計劃和運行時信息。

即使你的服務器很忙，如果設計了有良好的計劃重用率的查詢，因為編譯率較低，也可以使用Showplan XML For Query Compile事件。他只在有存儲過程或語句被編譯或重新編譯時才生成跟蹤記錄，但不包括運行時信息。

通過為各個列設置篩選值可以減少跟蹤的大小。在設置跟蹤篩選器時，只有ApplicationName、ClientProcessID、HostName、LogionName、LogionSid、NTDomainName、NTUserName和SPID這些列上應用篩選器會抑制時間的生成。其他篩選器只有在時間被生成并到達客戶端后才會應用，所以并不能減少服務器的開銷，事實上會造成更多的開銷。

另外，相對于在企業管理器中使用顯示計劃，顯示計劃跟蹤事件進一步擴大了SQL Server所能捕獲計劃的語句集合。如：CREATE、INSERT INTO … EXEC語句等。

從過程緩存中提取顯示計劃

上面已經介紹，SQL Server當生成計劃后，會把它保存到過程緩存之中。我們可以用幾個動態管理視圖和函數、DBCC PROCCACHE、以及目錄視圖sys.syscacheobjects來檢查過程緩存。

Sys.dm_exec_query_plan (DMF)以XML格式返回位于過程緩存的計劃。DMF要求一個計劃句柄作為唯一的參數。計劃句柄是一個VARBINARY(64)類型的查詢計劃標識符，DMV為當前過程緩存中每個查詢都可以返回該標識符。

看一個示例：

SELECT qplan.query_plan AS [Query Plan]

FROM sys.dm_exec_query_stats AS qstats

?CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan;

這個查詢為所有緩存的查詢計劃返回XML顯示計劃。

但是想這樣找到某個查詢計劃非常困難，因為查詢文本被包含在XML顯示計劃內部。下面的查詢使用Xquery value方法從顯示計劃中提取出序列號(No列)和查詢文本(Statement Text列)。因為每個批處理都有sql_handle，所以用ORDER BY sql_handle可以按這些語句在批處理中的順序進行排序顯示。

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)

SELECT

?C.value('@StatementId', 'INT') AS [No],

?C.value('(./@StatementText)', 'NVARCHAR(MAX)') AS [Statement Text],

?qplan.query_plan AS [Query Plan]

FROM (SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats) AS qstats

?CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan

?CROSS APPLY query_plan.nodes('/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/ sql:Statements/?descendant::*[attribute::StatementText]') AS T(C)

ORDER BY plan_handle, [No];

運行結果：

更新計劃

當優化INSERT、UPDATE、DELETE這些數據修改語句時，優化器必須要處理幾個特殊的問題。IUD計劃(INSERT、UPDATE、DELETE)包括兩個階段。

第一個階段：通過生成用于描述數據更改的數據流來確定哪些行將被IUD。對于INSERT，數據流包含列值，對于DELETE，它包含表鍵，對于UPDATE，他既包含表鍵也包含被修改的列的值。

第二個階段，把數據流中的描述的更改應用到表，通過執行約束驗證保證數據完整性，它維護非聚集索引和索引視圖，如果存在觸發器則引發觸發器。

UPDATE和DELETE查詢計劃通常還包含兩個對目標表的引用：第一個引用用于標識受影響的行，第二個引用執行更改的地方。INSERT計劃只包含一個對目標表的引用。

在一些簡單的情況是，SQL Server把IUD計劃的兩個階段合并在一起。如：把值直接插入表，成為標量插入，或者更新/刪除由目標表主鍵標識的行。

如果SQL Server需要執行約束驗證，則在第二個階段會自動包含Assert運算符。SQL Server通過在受影響的行和列上計算一個通常成本較低的標量表達式為INSERT和UPDATE驗證CHECK約束。

對包含外鍵約束的表執行INSERT和UPDATE會強制驗證CHKECK約束，而且對包含被引用的表所執行的INSERT和UPDATE也會強制驗證外鍵約束。為驗證約束，即使不是IUD操作目標的相關表也被掃描。聲明主鍵將自動地在該列創建唯一的索引，但外鍵不一樣，對被引用的外鍵列執行UPDATE和DELETE必須為每個被更新或刪除的主鍵值訪問外鍵表。如果這個約束是一個級聯引用完整性約束，那么將會執行更改，否則將驗證被刪除的鍵是否存在。因此，要對鍵值執行UPDATE或對主表執行DELETE，那么應該確保外鍵上存在索引。

在處理INSERT和DELETE語句時，除了在聚集索引或堆上執行IUD操作，還會維護所有非聚集索引，UPDATE查詢還維護包含被修改列的索引。因為非聚集索引包含聚集索引鍵和分區鍵以允許高效地訪問數據行，所以更新那些參與聚集索引鍵或分區鍵的列成本很高，因為它會修改或重建所有索引。更新分區鍵還會導致行在分區之間的移動。因此，如果可能的話，應該選擇不更新的列作為聚集鍵或分區鍵。

總的來說，IUD語句的性能與包含目標列的索引數量密切相關，因為他們將會被重建或修改。對索引執行單行INSERT和DELETE操作要求遍歷一次索引樹。SQL Server更新索引鍵和分區鍵的方法是先執行DELETE再執行INSERT，所以在索引操作上，UPDATE的成本比INSERT和DELETE要多一倍。

查詢優化器執行IUD語句時有兩種不同的策略：每行維護和每索引維護。

首先使用這個文件準備參考數據：3.rar

然后來看兩個查詢

查詢1：

DELETE FROM dbo.Orders WHERE OrderDate = '2002-01-01'

更新計劃：

這就是一個每行查詢的例子，SQL Server為該查詢所影響的每一行同時維護索引和基表(基表=堆或聚集索引)，并且對所有非聚集索引的更新將與對基表中每一行的更新同時執行。這個查詢計劃沒有對第2個索引執行任何刪除操作，因為這些工作在聚集索引刪除運算符的計算是同時完成。

查詢2：

DELETE FROM dbo.Orders WHERE OrderDate < '2006-01-01'

更新計劃：

這個查詢2與查詢1的更新計劃完全不同，因為它執行的是每索引維護。

首先，該計劃從聚集索引中刪除符合條件的行，同時構建一個臨時的假脫機表(spool table)，其中包含必須進行維護的三個非聚集索引的鍵值。SQL Server為每個索引讀取一次假脫機數據。在讀取假脫機數據和從非聚集索引中刪除行之間，SQL Server按被維護索引的順序排序假脫機數據，以確保對索引頁的最佳訪問。

Sequence運算符強制其分支的執行順序，在這里索引的按從上倒下的順序進行刪除。

每行更新策略在CPU方面是高效的，因為同時更新表和所有索引只需很短的代碼路徑。每索引維護的代碼稍微有些復雜，但這樣更節省I/O。如果對鍵排序后再單獨更新非聚集索引，即使同一頁中有許多行都被更新，我們也只須訪問索引頁一次。這也就是為什么SQL Server查詢優化器認為每行策略需要多次讀取被維護索引的同一頁才能完成維護，這時它通常選擇每索引維護策略。

轉載于:https://www.cnblogs.com/YinWangLive/archive/2009/04/15/1436650.html

總結

以上是生活随笔為你收集整理的TSQL查询内幕::(2.3)查询计划与更新计划的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： IE与Mozila FireFox 中的
下一篇： SQL中几个比较重要的系统表