炎龍牙

有時候閱讀別人的文章，在截圖裡面總是會看到以下畫面，有計算執行次數，有掃描計數…等等的資訊?? 可是自己的怎麼總是只有出現( ??個資料列受到影響)…?

T-SQL語句的撰寫是會影響到查詢時的效率，但我們該怎麼知道語法在執行上的效率??

1. 使用顯示執行計畫
2. 利用SQL語句來執行時間分析。
3. 看SSMS執行時右下角的經過時間。

事實上SQL Server 在查詢的過程中，會按下列順序進行

統計與分析執行計畫------>編譯語句------>執行------>顯示結果集

要顯示這些訊息，都跟設置Statistics的選項有關

 

Statistics Time

1. CPU時間

   CPU時間就是指這次執行的SQL語句，在CPU中進行運算所花費的時間(占用多少CPU時間)。如果Server是屬於多CPU，這個時間是指所有執行此語句的CPU花費的總和時間。這個時間的值會跟Profiler在追蹤資料時所記錄的CPU欄位值相同。

   

2. 經過時間

   經過時間就是指這次執行的語句，總共所花費的總和時間，其中包含了I/O的等待時間….等等，這個值會跟Profiler在追蹤資料時所記錄的Duration欄位值相同。

   

   也就是說，當整個SQL語句在執行的時候，全部的CPU時間，就會是

   SQL Server 剖析與編譯時間的CPU時間加上，SQL Server執行次數的CPU時間。

   整個語句的執行時間就會是剖析與編譯時間跟執行次數這兩個經過時間相加。(以下圖來說，整個SQL語句執行時間是 328 + 156 + 343 + 204 = 1031，CPU時間+經過時間)

   特別要注意的是，在有暫存的情況下，執行計畫是可以被重新利用的，在重複執行的過程中，資料也有可能不需要從重新從磁碟讀取，所以如果在執行次數的經過時間很短，表示有可能本次的語句查詢，使用了暫存的資料。

   

使用方式：

在SQL語句的前後加上

SET STATISTICS TIME OFF
GO

--SQL 語句--
SELECT * FROM TableName
GO

SET STATISTICS TIME OFF
GO

Statistics IO

顯示T-SQL語句磁碟活動的資料訊息

(648個資料列受到影響)

這是指資料影響筆數，我想應該大家都知道…..

1. 資料表：指的是資料表的名稱。
2. 掃描計數(Scan count)： 為了建構輸出的最終資料集，在達到分葉層級之後朝任何方向啟動以擷取所有值的搜尋/掃描次數。

   - 如果使用的索引是主索引鍵的唯一索引或叢集索引，而且您只要搜尋一個值，掃描計數就是 0。 例如 WHERE Primary_Key_Column = <value>。

   - 當您要使用在非主索引鍵資料行上定義的非唯一叢集索引來搜尋一個值時，掃描計數就是 1。 進行這項作業是為了檢查您所搜尋的索引鍵值是否有重複的值。 例如 WHERE Clustered_Index_Key_Column = <value>。

   - 當 N 是使用索引鍵找出索引鍵值之後，朝向分葉層級左側或右側啟動的不同搜尋/掃描次數時，掃描計數就是 N。

3. 邏輯讀取(Logical reads)：從資料快取中讀取的頁數。頁數越多，就是要取得的資料量越大，也就是消耗內部資源越大，查詢也就越沒有效率。

PS：這裡顯示的單位不是page也不是K、KB，是因為SQL Server在做讀寫的時候，會執行特定的程序，當每次執行一次這個程序，Read/Writre就會增加1，所以這個值越大，就表示SQL做了越多I/O，但是又不能計算出實際進行I/O的實際數量，所以這值就只能是一個邏輯估算值。

1. 實體讀取(physical reads)：從磁碟中讀取的頁數。
2. 讀取前讀取(read-ahead reads)：放入查詢快取中的頁數。

   實體讀取+ 讀取前讀取，就是SQL Server為了完成語句的查詢，從磁碟中讀取，如果不為0..就是資料並沒有存在暫存區，這是有一定程度會影響效能。

3. LOB邏輯讀取(lob logical reads)：從資料快取中讀取的 text、ntext、image 或大數值類型 (varchar(max)、nvarchar(max)、varbinary(max)) 頁數。
4. LOB實體讀取(lob physical reads)：從磁碟中讀取的 text、ntext、image 或大數值類型頁數。

LOB讀取前讀取(lob read-ahead reads)：放入查詢快取中的 text、ntext、image 或大數值類型頁數。

SET STATISTICS IO 的設定是在執行階段進行設定，而不是在剖析階段進行設定。

SET STATISTICS IO (Transact-SQL)

 

Statistics Profile

老實說這個指令很少用，因為就已經有執行計畫可以看了，但是有時候圖形介面在查找資料的時候不是很方便，這個可以幫助我們知道目前SQL語句的運作情況，理解SQL語句執行過程，分析語句需要調整的方向，也可以判斷SQL Server是否選擇了一個正確的執行計畫。

這個顯示的結果，其實應該是從下往上查看，也就是從最後的節點去往上查找每一段SQL語句，耗用了多少的資源與執行了多少個步驟。如圖

1. Rows：執行計畫中，每一步驟所返回的實際行數。
2. Executes：執行計畫中，每一步驟被執行了多少次。
3. StmtText：執行計畫的實際内容。執行計畫是採用樹狀的形式顯示。每一行都是一個步驟或是分支，都會有結果集返回，也都會有自己的執行成本。
4. StmtId：目前批次的陳述式號碼
5. NodeId：節點代碼。這部分的節點代碼與執行計畫中的【節點識別碼】相同。
6. Parent：父節點代碼。藉由此代碼可以知道執行計畫是怎麼產生分支。
7. PhysicalOp：節點的實體實作演算法。 只適用於 PLAN_ROWS 類型的資料列。
8. LogicalOp：這個節點所代表的關聯式代數運算子。 只適用於 PLAN_ROWS 類型的資料列。
9. Argument：提供所執行之作業的補充資訊。 這個資料行的內容會隨著實體運算子而不同。
10. DefinedValues：包含這個運算子導入的值清單 (以逗號分隔)。 這些值可能是在目前查詢中的計算運算式 (如在 SELECT 清單或 WHERE 子句中)，也可能是查詢處理器為了處理這項查詢而導入的內部值。 之後，就可以在這項查詢內的其他位置參考這些已定義的值。 只適用於 PLAN_ROWS 類型的資料列。
11. EstimateRows：SQL Server根據表格上的統計資訊，預估的每個步驟的返回行數。在分析執行計畫時，我們會經常將Rows和EstimateRows這兩列做對比，先確認SQL Server預估的是否正確，才能判斷統計資訊是否有更新。
12. EstimateIO：SQL Server根據EstimateRows和統計資訊裡記錄的欄位長度，預估的每一步會產生的I/O成本
13. EstimateCPU：SQL Server根據EstimateRows和統計資訊裡記錄的欄位長度，以及要做的事情的複雜度，預估每一步會產生的CPU執行成本
14. AvgRowSize：這個運算子所處理之資料列的估計平均資料列大小 (以位元組為單位)。
15. TotalSubtreeCost：SQL Server根據EstimateIO和EstimateCPU通過某種計算公式，計算出每一步執行計畫與子樹的執行成本（包括這一步自己的成本和他的所有下層步驟的執行成本總和），也就是如果要知道這步驟的執行成本，需要在減去下層子樹的執行成本。成本單位是根據內部時間度量，而不是根據時鐘的時間。
16. Warnings：SQL Server在執行每一步驟時遇到的警告訊息，例如，某一步沒有統計資訊支援cost預估等。
17. Type：節點類型。 對每項查詢的父節點而言，這都是 Transact-SQL 陳述式類型 (如 SELECT、INSERT、EXECUTE 等等)。 對於代表執行計畫的子節點而言，類型是 PLAN_ROW。
18. Parallel：執行計畫的這一步是不是使用了並行的執行計畫。0 = 運算子並未並行執行。1 = 運算子正在並行執行。
19. EstimateExecutions：執行目前查詢時，將執行這個運算子的估計次數。

 

SET SHOWPLAN_ALL (Transact-SQL)

 

 

參考文件：

MSDN

SQL 語句執行時間分析

SQL Server读懂语句运行的统计信息 SET STATISTICS TIME IO PROFILE ON

水滴可成涓流，涓流可成湖泊大海。
汲取累積知識，將知識堆積成常識；將常識探究成學識；將學識簡化為知識；授人自省。