對于語句的運(yùn)行，除了執(zhí)行計劃本身，還有一些其他因素要考慮，例如語句的編譯時間、執(zhí)行時間、做了多少次磁盤讀等。
如果DBA能夠把問題語句單獨(dú)測試運(yùn)行，可以在運(yùn)行前打開下面這三個開關(guān)，收集語句運(yùn)行的統(tǒng)計信息。
這些信息對分析問題很有價值。
復(fù)制代碼 代碼如下:
SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
--------------------------------------------------------------------------------
請先來看看SET STATISTICS TIME ON會返回什么信息。先運(yùn)行語句：
復(fù)制代碼 代碼如下:
DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有緩存數(shù)據(jù)
DBCC freeproccache
GO
--清除buffer pool里的所有緩存的執(zhí)行計劃
SET STATISTICS TIME ON
GO
USE [AdventureWorks]
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
SET STATISTICS TIME OFF
GO
除了結(jié)果集之外，SQLSERVER還會返回下面這兩段信息
復(fù)制代碼 代碼如下:
SQL Server 分析和編譯時間:
CPU 時間 = 15 毫秒，占用時間 = 104 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
(4 行受影響)
SQL Server 執(zhí)行時間:
CPU 時間 = 171 毫秒，占用時間 = 1903 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
大家知道SQLSERVER執(zhí)行語句是分以下階段：分析-》編譯-》執(zhí)行
根據(jù)表格的統(tǒng)計信息分析出比較合適的執(zhí)行計劃，然后編譯語句，最后執(zhí)行語句
下面說一下上面的輸出是什么意思：
--------------------------------------------------------------------------------
1、CPU時間 ：這個值的含義指的是在這一步，SQLSERVER所花的純CPU時間是多少。也就是說，語句花了多少CPU資源
2、占用時間 ：此值指這一步一共用了多少時間。也就是說，這是語句運(yùn)行的時間長短，有些動作會發(fā)生I/O操作，產(chǎn)生了I/O等待，或者是遇到阻塞、產(chǎn)生了阻塞等待。總之時間用掉了，但是沒有用CPU資源。所以占用時間比CPU時間長是很正常的 ，但是CPU時間是語句在所有CPU上的時間總和。如果語句使用了多顆CPU，而其他等待幾乎沒有，那么CPU時間大于占用時間也是正常的
3、分析和編譯時間：這一步，就是語句的編譯時間。由于語句運(yùn)行之前清空了所有執(zhí)行計劃，SQLSERVER必須要對他編譯。
這里的編譯時間就不為0了。由于編譯主要是CPU的運(yùn)算，所以一般CPU時間和占用時間是差不多的。如果這里相差比較大，就有必要看看SQLSERVER在系統(tǒng)資源上有沒有瓶頸了。
這里他們是一個15毫秒，一個是104毫秒
4、SQLSERVER執(zhí)行時間： 語句真正運(yùn)行的時間。由于語句是第一次運(yùn)行，SQLSERVER需要把數(shù)據(jù)從磁盤讀到內(nèi)存里，這里語句的運(yùn)行發(fā)生了比較長的I/O等待。所以這里的CPU時間和占用時間差別就很大了，一個是171毫秒，而另一個是1903毫秒
總的來講，這條語句花了104+1903+186=2193毫秒，其中CPU時間為15+171=186毫秒。語句的主要時間應(yīng)該是都花在了I/O等待上
現(xiàn)在再做一遍語句，但是不清除任何緩存
復(fù)制代碼 代碼如下:
SET STATISTICS TIME ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
SET STATISTICS TIME OFF
GO
這次比上次快很多。輸出時間統(tǒng)計信息是：
復(fù)制代碼 代碼如下:
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
(4 行受影響)
SQL Server 執(zhí)行時間:
CPU 時間 = 156 毫秒，占用時間 = 169 毫秒。
SQL Server 分析和編譯時間:
CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
由于執(zhí)行計劃被重用，“SQL分析和編譯時間” CPU時間是0，占用時間是0
由于數(shù)據(jù)已經(jīng)緩存在內(nèi)存里，不需要從磁盤上讀取，SQL執(zhí)行時間 CPU時間是156，占用時間這次和CPU時間非常接近，是169。
這里省下運(yùn)行時間1903-169=1734毫秒，從這里可以再次看出，緩存對語句執(zhí)行性能起著至關(guān)重要的作用
為了不影響其他測試，請運(yùn)行下面的語句關(guān)閉SET STATISTICS TIME ON
復(fù)制代碼 代碼如下:
SET STATISTICS TIME OFF
GO
SET STATISTICS IO ON
--------------------------------------------------------------------------------
這個開關(guān)能夠輸出語句做的物理讀和邏輯讀的數(shù)目。對分析語句的復(fù)雜度有很重要的作用
還是以剛才那個查詢作為例子
復(fù)制代碼 代碼如下:
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
他的返回是：
(4 行受影響)
表 'SalesOrderDetail_test'。掃描計數(shù) 5，邏輯讀取 15064 次，物理讀取 0 次，預(yù)讀 15064 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
各個輸出的含義是：
--------------------------------------------------------------------------------
表：表的名稱。這里的表就是SalesOrderDetail_test
掃描計數(shù)：執(zhí)行的掃描次數(shù)。按照執(zhí)行計劃，表格被掃描了幾次。一般來講大表掃描的次數(shù)越多越不好。唯一的例外是如果執(zhí)行計劃選擇了并發(fā)運(yùn)行， 由多個thread線程同時做一個表的讀取，每個thread讀其中的一部分，但是這里會顯示所有thread的數(shù)目。也就是有幾個thread在并發(fā)做， 就會有幾個掃描。這時數(shù)目大一點沒問題的。
邏輯讀取：從數(shù)據(jù)緩存讀取的頁數(shù)。頁數(shù)越多，說明查詢要訪問的數(shù)據(jù)量就越大，內(nèi)存消耗量越大，查詢也就越昂貴。
可以檢查是否應(yīng)該調(diào)整索引，減少掃描的次數(shù)，縮小掃描范圍
物理讀取：從磁盤讀取的頁數(shù)
預(yù)讀：為進(jìn)行查詢而預(yù)讀入緩存的頁數(shù)
物理讀取+預(yù)讀：就是SQLSERVER為了完成這句查詢而從磁盤上讀取的頁數(shù)。如果不為0，說明數(shù)據(jù)沒有緩存在內(nèi)存里。運(yùn)行速度一定會受到影響
LOB邏輯讀取：從數(shù)據(jù)緩存讀取的text、ntext、image、大值類型（varchar(max)、nvarchar(max)、varbinary(max)）頁的數(shù)目
LOB物理讀取：從磁盤讀取的text、ntext、image、大值類型頁的數(shù)目
LOB預(yù)讀：為進(jìn)行查詢而放入緩存的text、ntext、image、大值類型頁的數(shù)目
然后再來運(yùn)行一遍，不清空緩存
復(fù)制代碼 代碼如下:
SET STATISTICS IO ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
結(jié)果集返回：
復(fù)制代碼 代碼如下:
1 表 'SalesOrderDetail_test'。掃描計數(shù) 5，邏輯讀取 15064 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，
2 lob 物理讀取 0 次，lob 預(yù)讀 0 次。
這次邏輯讀取不變，還是15064頁。但是物理讀取和預(yù)讀都是0了。說明數(shù)據(jù)已經(jīng)緩存在內(nèi)存里第二次運(yùn)行不需要再從磁盤上讀一遍，節(jié)省了時間為了不影響其他測試，請運(yùn)行下面語句關(guān)閉SET STATISTICS IO ON
復(fù)制代碼 代碼如下:
SET STATISTICS IO OFF
GO
SET STATISTICS PROFILE ON
--------------------------------------------------------------------------------
這是三個設(shè)置中返回最復(fù)雜的一個，他返回語句的執(zhí)行計劃，以及語句運(yùn)行在每一步的實際返回行數(shù)統(tǒng)計。
通過這個結(jié)果，不僅可以得到執(zhí)行計劃，理解語句執(zhí)行過程，分析語句調(diào)優(yōu)方向，也可以判斷SQLSERVER是否
選擇了一個正確的執(zhí)行計劃。
復(fù)制代碼 代碼如下:
SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[SalesOrderID])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]53660
GO
返回的結(jié)果集很長，下面說一下重要字段
--------------------------------------------------------------------------------
注意：這里是從最下面開始向上看的，也就是說從最下面開始一直執(zhí)行直到得到結(jié)果集所以（行1）里的rows字段顯示的值就是這個查詢返回的結(jié)果集。
而且有多少行表明SQLSERVER執(zhí)行了多少個步驟,這里有6行，表明SQLSRVER執(zhí)行了6個步驟！！
Rows:執(zhí)行計劃的每一步返回的實際行數(shù)
Executes:執(zhí)行計劃的每一步被運(yùn)行了多少次
StmtText:執(zhí)行計劃的具體內(nèi)容。執(zhí)行計劃以一棵樹的形式顯示。每一行都是運(yùn)行的一步，都會有結(jié)果集返回，也都會有自己的cost
EstimateRows:SQLSERVER根據(jù)表格上的統(tǒng)計信息，預(yù)估的每一步的返回行數(shù)。在分析執(zhí)行計劃時，我們會經(jīng)常將Rows和EstimateRows這兩列做對比，先確認(rèn)SQLSERVER預(yù)估得是否正確，以判斷統(tǒng)計信息是否有更新
EstimateIO:SQLSERVER根據(jù)EstimateRows和統(tǒng)計信息里記錄的字段長度，預(yù)估的每一步會產(chǎn)生的I/O cost
EstimateCPU:SQLSERVR根據(jù)EstimateRows和統(tǒng)計信息里記錄的字段長度，以及要做的事情的復(fù)雜度，預(yù)估每一步會產(chǎn)生的CPU cost
TotalSubtreeCost:SQLSERVER根據(jù)EstimateIO和EstimateCPU通過某種計算公式，計算出每一步執(zhí)行計劃子樹的cost （包括這一步自己的cost和他的所有下層步驟的cost總和），下面介紹的cost說的都是這個字段值
Warnings:SQLSERVER在運(yùn)行每一步時遇到的警告，例如，某一步?jīng)]有統(tǒng)計信息支持cost預(yù)估等。
Parallel:執(zhí)行計劃的這一步是不是使用了并行的執(zhí)行計劃
從上面結(jié)果可以看出執(zhí)行計劃分成4步，其中第一步又分成并列的兩個子步驟
步驟a1（第5行）：從[SalesOrderHeader_test]表里找出所有a.[SalesOrderID]>43659 AND a.[SalesOrderID]53660的值
因為表在這個字段上有一個聚集索引，所以SQL可以直接使用這個索引的seek
SQL預(yù)測返回10000條記錄，實際也就返回了10000條記錄.。這個預(yù)測是準(zhǔn)確的。這一步的cost是0.202(totalsubtreecost)
步驟a2（第6行）：從[SalesOrderDetail_test]表里找出所有 a.[SalesOrderID]>43659 AND a.[SalesOrderID]53660的值
因為表在這個字段上有一個非聚集索引，所以SQL可以直接使用這個索引的seek 這里能夠看出SQL聰明的地方。雖然查詢語句只定義了[SalesOrderHeader_test]表上有a.[SalesOrderID]>43659 AND a.[SalesOrderID]53660過濾條件，但是根據(jù)語義分析，SQL知道這個條件在[SalesOrderDetail_test]上也為真。所以SQL選擇先把這個條件過濾然后再做join。這樣能夠大大降低join的cost
在這一步SQL預(yù)估返回50561條記錄，實際返回50577條。cost是0.127，也不高
步驟b（第4行）：將a1和a2兩步得到的結(jié)果集做一個join。因為SQL通過預(yù)估知道這兩個結(jié)果集比較大，所以他直接選擇了Hash Match的join方法。
SQL預(yù)估這個join能返回50313行，實際返回50577行。因為SQL在兩張表的[SalesOrderID]上都有統(tǒng)計信息，所以這里的預(yù)估非常準(zhǔn)確
這一步的cost等于totalsubtreecost減去他的子步驟，0.715-0.202-0.127=0.386。由于預(yù)估值非常準(zhǔn)確，可以相信這里的cost就是實際每一步的cost
步驟c（第3行）：在join返回的結(jié)果集基礎(chǔ)上算count(*)的值這一步比較簡單，count(*)的結(jié)果總是1，所以預(yù)測值是正確的。
其實這一步的cost是根據(jù)上一步（b）join返回的結(jié)果集大小預(yù)估出來的。我們知道步驟b的預(yù)估返回值非常準(zhǔn)確，所以這一步的預(yù)估cost也不會有什么大問題
這棵子樹的cost是0.745，減去他的子節(jié)點cost，他自己的cost是0.745-0.715=0.03。是花費(fèi)很小的一步
步驟b（第2行）：將步驟c返回的值轉(zhuǎn)換為int類型，作為結(jié)果返回
這一步是上一步的繼續(xù)，更為簡單。convert一個值的數(shù)據(jù)類型所要的cost幾乎可以忽略不計。所以這棵子樹的cost和他的子節(jié)點相等，都是0.745。
也就是說，他自己的cost是0
通過這樣的方法，用戶可以了解到語句的執(zhí)行計劃、SQLSERVER預(yù)估的準(zhǔn)確性、cost的分布
最后說一下：不同SQLSERVER版本，不同機(jī)器cost可能會不一樣，例如SQL2005 ,SQL2008
您可能感興趣的文章:- SQL Server自動更新統(tǒng)計信息的基本算法
- sqlserver 統(tǒng)計sql語句大全收藏
- SQLSERVER語句的執(zhí)行時間顯示的統(tǒng)計結(jié)果是什么意思
- 淺談SQL Server中統(tǒng)計對于查詢的影響分析
- SQLServer2005 中的幾個統(tǒng)計技巧
- SQL Server統(tǒng)計信息更新時采樣百分比對數(shù)據(jù)預(yù)估準(zhǔn)確性的影響詳解
