SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）(轉)

標籤：

前言

上一篇我們分析了查詢最佳化工具的工作方式，其中包括：查詢最佳化工具的詳細運行步驟、篩選條件分析、索引項目最佳化等資訊。

本篇我們分析在我們啟動並執行過程中幾個關鍵計量值的檢測。

通過這些指標值來分析語句的運行問題，並且分析其最佳化方式。

通過本篇我們可以學習到調優中經常利用的幾個利器！

廢話少說，開始本篇的正題。

技術準備

資料庫版本為SQL Server2008R2，利用微軟的一個更簡潔的案例庫（Northwind）進行分析。

 

利器一、IO統計

通過這個IO統計能為我們分析出當前查詢語句所要掃描的資料頁的數量。這裡面有幾個重要的概念，我們依次分析。

方法很簡單，一行代碼搞定：

SET STATISTICS IO ON

來看個例子

SET STATISTICS IO ONGOSELECT * FROM Person.Contact

這裡可以看到這個語句對於資料表的操作次數，基於資料頁的掃描項。

所謂的資料頁就是資料庫的底層資料存放區方式，SQL Server以資料頁的形式儲存表行資料。每個資料頁為8K，

8K=8192位元組-96位元組（頁頭）-36位元組（行位移）=8060位元組

也就說一個資料頁儲存的純資料內容為8060位元組。

我們依次來解釋上面出現幾個讀取的概念：

邏輯讀

表示處理查詢所需要訪問頁的總數。也就是說要完成一個查詢語句需要讀取的資料頁的總數。

這裡的資料頁有可能來自記憶體，也有可能來自硬碟讀取。

物理讀

這個就是說來自硬碟讀取的資料頁數。我們知道SQL Server每次都會將讀取的資料頁儘可能存在於記憶體中，以方便下一次直接讀取，提升讀取速度。

所以在這裡關於儲存於記憶體中的資料頁下次訪問的機率，提出了一個指標：快取命中率

快取命中率=(邏輯讀—物理讀)/邏輯讀

提出這個指標的提出其實就是為了衡量記憶體中緩衝的資料頁的有效性。比如：假如緩衝與記憶體中的資料頁就使用一次就不使用了，對於這種就應該及時從記憶體中清除掉，畢竟對於記憶體資源來說是非常昂貴的。應該用它來快取命中率高的資料頁。

預讀

預讀其實就是SQL語句在最佳化的時候預先讀取到記憶體中的資料頁數。這個預先讀取的資料頁是提前評估出來的，也就是上一篇我們文章中介紹的查詢最佳化工具要做的事情。

當然，這些預讀的資料頁有時候不是所有的都要用到，但是它基本能涵蓋到查詢用到的資料頁。

這裡要提示一下，預讀資料是通過另外一個線程進行讀取的和語句最佳化線程非用同一線程，並行運行，目的是快速擷取資料，提升查詢擷取的速度。

從這個指標我們可以分析出很多問題，來舉個例子：

我們新添加一張測試表，指令碼如下

--執行下面指令碼新產生一張表SELECT * INTO NewOrdersFROM OrdersGO--新增加一列ALTER TABLE NewOrdersADD Full_Details CHAR(2000) NOT NULL DEFAULT ‘full details‘GO

然後利用如下指令碼來看下這張表的大小

EXEC sp_spaceused NewOrders,TRUEGO

我們可以看到這張表資料頁的總大小為2216KB，我們知道一頁為8KB，可以推斷出這個表的資料頁有：

2216（資料頁總大小）/8(一個資料頁大小)=277頁

也就是說這個資料表有277個資料頁。

當然，我們也可以通過如下DMV視圖來查看該頁的資料頁數

SELECT * FROM SYS.dm_db_index_physical_stats(DB_ID(‘Northwind‘),object_id(‘NewOrders‘),NULL,NULL,‘detailed‘)

經過上面的分析，

我們可以推測，在查詢這張表做全表掃描的時候，理論的資料頁的邏輯讀數就應該為277次

通過如下語句驗證下

--先清空快取資料，生產機慎用DBCC DROPCLEANBUFFERSSET STATISTICS IO ONSELECT * FROM NewOrders

我去...

這裡的邏輯讀取為1047頁，和我們上面的推斷277頁不相符...擦...神馬原因！！！

這裡就是我們要分析的資料頁Forwarded record現象造成的。因為我們在建立立的表，在後面新添加的一列資料：Full_Details，類型為CHAR(2000)的資料列，當資料行中的變長列增長使得原有頁無法容納下資料行時，資料將會移動到新的頁中，並在原位置留下一個指向新頁的指標，這就是所謂的： Forwarded record

 

我們可以通過如下DMV視圖，查看該表的Forwarded Record形成的頁有多少

SELECT * FROM SYS.dm_db_index_physical_stats(DB_ID(N‘Northwind‘),object_id(‘NewOrders‘),NULL,NULL,‘detailed‘)

糾正一下：的770資料頁為Forwarded Record頁，非拆分頁的概念（感謝院友 wy123 指出）。

看到了，這裡的Forwarded Record頁為770頁，那麼我們就可以推測出我們的邏輯讀數量來了

277（原資料頁）+770（Forwarded Record頁）=1047頁

所以上面的我們的問題就分析出原因了。

我們通過此表也展示了一個Forwarded Record頁的問題：會影響查詢效能。

解決的方式很多種，最簡單的方式就是重建叢集索引。

CREATE CLUSTERED INDEX orderID_C ON NewOrders(OrderID)GODROP INDEX NewOrders.orderID_CGOSET STATISTICS IO ONSELECT * FROM NewOrdersGO

通過IO統計項，除了可以分析出上面的Forwarded Record頁造成的片段外，更重要的地方使用來對比不同查詢語句之間的讀取次數，通過降低讀取的次數來最佳化語句。

 

關於預讀的情況，我們在前面已經分析了，其資料時通過另外一個線程在T-SQL查詢語句最佳化的時候進行資料的預先載入。

所以這個線程在預讀資料的時候其實是有一個參考值的，根據這個參考值讀取出來的資料才能保證大部分資料是有用的，也就是提高上面提到的快取命中率。

關於這個參考值，我分析了下，其實是分為兩中情況分析的。

 

首先、如果是資料表為堆表，SQL Server擷取的方式只能通過全表掃描了。而此方式為了避免重複讀取，增加消耗，所以一次的預讀並非讀取一個資料頁，

而是一段物理上的連續64個頁

來看聯機叢書的官方解釋：

預讀機制允許資料庫引擎從一個檔案中讀取最多 64 個連續頁 (512KB)。該讀取作為緩衝區快取中相應數量（可能是非相鄰的）緩衝區的一次散播-聚集讀取來執行。如果此範圍內的任何頁在緩衝區快取中已存 在，當讀取完成時，所讀取的相應頁將被放棄。如果相應頁在緩衝中已存在，也可以從任何一端“裁剪”頁的範圍。

所以，如果我們的表在物理上不是連續頁，那麼讀取次數就不好怎麼確定了。

我們來看個堆表的例子

SET STATISTICS IO ON--建立個測試表SELECT * INTO NewOrders_TEST FROM NewOrdersSELECT * FROM NewOrders_TEST

這裡預讀的次數為8次，所以我估計底層的資料頁肯定不是連續的。所以造成了多出了3次。

我們可以DBCC IND()進行查詢下，來驗證下我的推斷。

DBCC IND(‘Northwind‘,‘NewOrders_TEST‘,1)

資料資訊比較多，我將其粘貼到Excel中，然後做了一個折線圖，其中塗掉的部分其實是沒有資料頁的，所以不會產生一次讀取。

關於讀取順序標示的也有點問題，不過確定的總數肯定是8次.....

希望這種方式，各位看官能看懂了...希望我也表述明白了。

 

其次、如果表非堆表，也就是說存在叢集索引項，那麼好了，SQL Server很輕鬆的找到了它預讀的參考依據：統計資訊。

並且，我們知道資料以B-Tree數儲存，讀取的資料頁都存在與葉子節點。所以基本沒有了什麼連續讀取的感念。

一個葉子節點就是一個資料頁，一個資料頁就是一次預讀。

來看個例子：

我們將上面的表添加上叢集索引項，再一次清空緩衝，執行查詢，指令碼如下

CREATE CLUSTERED INDEX NewOrders_TESTIndex ON NewOrders_TEST(OrderID)GOSELECT * FROM NewOrders_TEST

這裡添加了叢集索引，SQL Server彷彿一下看到了救星，根據統計資訊，預讀資料就可以。

所以如果統計資訊有錯誤，就造成了預讀的亂讀取....然後嚴重降低了快取命中率.....然後嚴重增加了記憶體中換出換入的速度....增加了CPU....

好了，咱們繼續文章，上面我們提到的這個預讀資料行，可以在如下DMV中查到。

SELECT * FROM SYS.dm_db_index_physical_stats(DB_ID(N‘Northwind‘),object_id(‘NewOrders_TEST‘),NULL,NULL,‘detailed‘)

從這個DMV視圖中可以看到這種表統計資訊為277個資料頁，所以形成了277次預讀。

但是，事實這個資料表是279頁，也就是說統計的資訊有問題，造成了少讀讀取了2個資料頁，而為了彌補這個統計過失就出現了2次物理讀，重新從硬碟中擷取。 

 

利器二、時間統計

關於時間統計這個很簡單，就是統計T-SQL執行語句執行時間項，包括CPU佔用時間、語句編譯時間、語句執行總時間等項。

使用方法也很簡單，一行代碼

SET STATISTICS TIME ON

通過這個參數，可以分析出以上資訊，其作用主要是用來對比查詢語句調優中的執行時間，我們的目標就是降低執行時間。

舉例：我們通過開啟時間統計，來對比下，上面的查詢語句，在第一次運行和以後運行（資料已經緩衝）的時間對比，瞭解下緩衝的重要性

再次執行的時間

緩衝追蹤（補充於2014年12月25日）

當然我們也可以再深入一點，如果想查看該部分資料在記憶體中緩衝的明細，可以通過如下DMV指令碼查看

SELECT * FROM sys.dm_os_buffer_descriptorsWHERE DB_NAME(database_id)=‘Northwind‘AND page_type=‘DATA_PAGE‘ORDER BY page_id 

也可以通過該DMV分析出各個庫在記憶體中佔據的大小比例，指令碼如下：

--清除緩衝dbcc dropcleanbuffers--查看緩衝內容中在記憶體大小SELECT COUNT(*)*8/1024 as ‘Cached Size(MB)‘       ,CASE database_id         WHEN 32767 THEN ‘ResourceDB‘        ELSE DB_NAME(database_id)        END AS ‘Database‘FROM sys.dm_os_buffer_descriptorsGROUP BY DB_NAME(database_id),database_idORDER BY ‘Cached Size(MB)‘ DESC

經過這次查詢，這張表已經全部緩衝到記憶體裡了，因為整張表總共就2MB的大小

 

本人轉自：http://www.cnblogs.com/zhijianliutang/p/4179110.html

指尖流淌

SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）(轉)