SQL Server調優系列進階篇（查詢語句運行幾個指標值監測） - zhijianliutang－IT工程師數位筆記本

文章出處

前言

上一篇我們分析了查詢優化器的工作方式，其中包括：查詢優化器的詳細運行步驟、篩選條件分析、索引項優化等信息。

本篇我們分析在我們運行的過程中幾個關鍵指標值的檢測。

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

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

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

技術準備

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

利器一、IO統計

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

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

SET STATISTICS IO ON

來看個例子

SET STATISTICS IO ON
GO
SELECT * FROM Person.Contact

這里可以看到這個語句對于數據表的操作次數，基于數據頁的掃描項。

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

8K=8192字節-96字節（頁頭）-36字節（行偏移）=8060字節

也就說一個數據頁存儲的純數據內容為8060字節。

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

邏輯讀

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

這里的數據頁有可能來自內存，也有可能來自硬盤讀取。

物理讀

這個就是說來自硬盤讀取的數據頁數。我們知道SQL Server每次都會將讀取的數據頁盡可能存在于內存中，以方便下一次直接讀取，提升讀取速度。

所以在這里關于存儲于內存中的數據頁下次訪問的概率，提出了一個指標：緩存命中率

緩存命中率=(邏輯讀—物理讀)/邏輯讀

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

預讀

預讀其實就是SQL語句在優化的時候預先讀取到內存中的數據頁數。這個預先讀取的數據頁是提前評估出來的，也就是上一篇我們文章中介紹的查詢優化器要做的事情。

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

這里要提示一下，預讀數據是通過另外一個線程進行讀取的和語句優化線程非用同一線程，并行運行，目的是快速獲取數據，提升查詢獲取的速度。

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

我們新添加一張測試表，腳本如下

--執行下面腳本新生成一張表
SELECT * 
INTO NewOrders
FROM Orders
GO
--新增加一列
ALTER TABLE NewOrders
ADD Full_Details CHAR(2000) NOT NULL DEFAULT 'full details'
GO

然后利用如下腳本來看下這張表的大小

EXEC sp_spaceused NewOrders,TRUE
GO

我們可以看到這張表數據頁的總大小為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 DROPCLEANBUFFERS

SET STATISTICS IO ON

SELECT * 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)
GO
DROP INDEX NewOrders.orderID_C
GO
SET STATISTICS IO ON
SELECT * FROM NewOrders
GO

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

關于預讀的情況，我們在前面已經分析了，其數據時通過另外一個線程在T-SQL查詢語句優化的時候進行數據的預加載。

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

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

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

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

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

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

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

我們來看個堆表的例子

SET STATISTICS IO ON
--新建個測試表
SELECT * INTO NewOrders_TEST FROM NewOrders
SELECT * 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)
GO
SELECT * 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_descriptors
WHERE 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_descriptors
GROUP BY DB_NAME(database_id),database_id
ORDER BY 'Cached Size(MB)' DESC