SQL Server 查詢效能最佳化——堆表、片段與索引（二）

 本文是對 SQL Server 查詢效能最佳化——堆表、片段與索引（一）的一些總結。

 第一：先對 SQL Server 查詢效能最佳化——堆表、片段與索引（一）中的例一的SET STATISTICS IO之後出現的關鍵資訊如下

表 'T_EPZ_INOUT_ENTRY_DETAIL'。掃描計數 1，邏輯讀 4825 次，物理讀 6 次，預讀 19672 次。

這句解釋一下。(有些內容來自網路，有些內部是自己的理解。)

 

SQL SERVER 資料庫引擎當遇到一個查詢語句時，SQL SERVER資料庫引擎會分別產生執行計畫(佔用CPU和記憶體資源)，同時儲存引擎讀取 IAM 以產生必須要讀取的磁碟地址排序列表。這使 SQL Server 得以將其 I/O 最佳化為大型有序讀取，根據它們在磁碟上的位置按順序完成。磁碟中取得需要取的資料(佔用I/O資源，這就是預讀)，注 意，兩個步驟是並行的，SQL SERVER通過這種方式可以讓計算和 I/O 重疊進行，從而充分利用 CPU 和磁碟，從而提高效能。

 

掃描計數：查詢資料時對涉及到的表被 訪問次數或涉及到的索引的掃描次數。在我們的例子中，不管是表掃描（例一與例二）還是索引掃描（例三）其中的表或索引都只被訪問了1次，由於查詢中不包括串連命令，這一資訊並不是十分有用，但如果查詢中包含有一個或多個串連，則這一資訊是十分有用的。
　　一個迴圈外部的表的掃描計數值為1，但對於一個迴圈內的表而言，其值為迴圈的次數。可以想象得到，對於一個迴圈內的表而言，其 掃描計數值越小，它所使用的資源越少，查詢的效能也就越高。因此在調節一個帶串連的查詢的效能時，需要關注掃描計數的值，在進行調節 時，注意觀察它是增加還是減少了。

邏輯讀： 這是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的資料。我們知道，SQL Server在對任何資料進行操作前，必須首先從磁碟中讀取資料所在的資料頁或索引頁，並把資料頁或索引頁存到資料緩衝區快取中。
　　那麼邏輯讀的意義是什麼呢？邏輯讀是指SQL Server為得到查詢中的結果而必須從資料緩衝區快取讀取的頁數。在執行查詢時，SQL Server不會讀取比實際需求多或少的資料， 因此，當在相同的資料集上執行同一個查詢，得到的邏輯讀的數字總是相同的。
　　為什麼說在調節查詢效能中知道SQL Server執行查詢時的邏輯讀值是很重要的呢？因為在每次執行同一查詢時，這個數值是不會變化的。因此，在進行查詢效能的調節時，這是一個可以用來衡量你的調節措施是否成功的一個很好的標準。
　　在對查詢的效能進行調節時，如果邏輯讀值下降，就表明查詢使用的伺服器資源減少，查詢的效能有所提高。如果邏輯讀值增加，則表示調節措施降低了查詢的效能。在其他條件不變的情況下，一個查詢使用的邏輯讀越少，其效率就越高，查詢的速度就越快。

物理讀：在這裡我要說的的東西可能初聽起來有點自相矛盾，但只要反覆思考，就會明白其中的真正含意。
　　物理讀指的是，在執行真正的查詢操作前，SQL Server必須從磁碟上向資料緩衝區快取中讀取它所需要的資料。在SQL Server開始執行查詢前，它要作的第一件事就是檢查它所需要的資料是否在資料緩衝區快取中，如果在，就從中讀取，如果不在，SQL Server必須首先將它需要的資料從磁碟上讀到資料緩衝區快取中。
　　我們可以想象得到，SQL Server在執行物理讀時比執行邏輯讀需要更多的伺服器資源。因此，在理想情況下，我們應當盡量避免物理讀操作。
　　下面的這一部分聽起來讓人容易感到糊塗了。在對查詢的效能進行調節時，可以忽略物理讀而只專註於邏輯讀。你一定會納悶兒，剛才不是還說物理讀比邏輯讀需要更多的伺服器資源嗎？
　　情況確實是這樣，SQL Server在執行查詢時所需要的物理讀次數不可能通過效能調節而減少的。減少物理讀的次數是DBA的一項重要工作，但它涉及到整個伺服器效能的調節，而不僅僅是查詢效能的調節。在進行查詢效能調節時，我們不能控制資料緩衝區快取的大小或伺服器的忙碌程度以及完成查詢所需要的資料是在資料緩衝區中還是在磁碟上，唯一我們能夠控制的資料是得到查詢結果所需要執行的邏輯讀的次數。
　　因此，在查詢效能的調節中，我們可以心安理得地不理會SET STATISTICS IO命令提供的物理讀的值。（減少物理讀次數、加快SQL Server運行速度的一種方式是確保伺服器的實體記憶體足夠多。）

預讀：與 物理讀一樣，這個值在查詢效能調節中也沒有什麼用處。預讀表示SQL Server在執行預讀機制時從磁碟上讀取的資料頁或索引頁。為了最佳化其效能，SQL Server資料引擎首先預測執行查詢執行計畫所需的資料和索引頁，然後在查詢實際使用這些頁之前將它們讀入緩衝區快取。根據SQL Server對資料 需求預測的準確程度，預讀的資料頁可能有用，也可能沒用。

備忘：一個緩衝區就是一個 8KB 大小的記憶體頁

 

以上文字大部分來源自網路，本人對其中部分不認同處，或有自己的理解處，做了少量修改。

 

　

 

第二：關於片段對於效能影響的結論：

SQL Server 中資料存放區的基本單位是頁，一頁包含8KB資料。磁碟 I/O 操作在頁級執行。也就是說，SQL Server 讀取或寫入的基本單位是資料頁。連續的8個頁面組成一個區（extent）。資料的insert和update操作可以引起頁面分割產生片段。如果分割後的兩個頁面在同一個區內，這種片段稱為內部片段，如果分割後的兩個頁面處於不同的區內，這種片段稱為外部片段。

一、內部片段和外部片段對資料檢索效能都有負面影響。

1. 內部片段的產生使資料稀疏的分布在大量的頁面中，這增加了讀取頁面到記憶體中所需的磁碟I/O操作，增加了從記憶體中檢索資料的邏輯讀取數量。

2. 外部片段導致磁碟上的索引頁面不連續，新的葉子頁面和原始葉子頁面離得很遠，物理順序與邏輯順序不同。為了更好的效能，首選順序I/O，因為這能在一個磁碟I/O讀取整個區（8個8KB頁面）。非連續的頁面需要非順序或者隨機I/O操作來從磁碟讀取資料，一個隨機I/O只能讀取一個頁面（8KB）。

 

二、在堆表中，當刪除資料鏈中間的記錄行時，會出現空頁,隨著空頁的累積，區的利用率也會下降，從而出現內部片段與外部片段。帶索引的表也有可能出現外部片段，如在現有的叢集索引中插入一行，這行正好導致現有的頁空間無法滿足容納新的行，從而導致了分頁，如果分頁後的兩個頁面正好分布在兩個區，就是外部片段。當有外部片段存在，會出現以下問題：對錶進行處理時，常常出現死結；利用較大的I／O操作或增加I/O緩衝區的大小也無法改變較慢的I／O速度；行操作的爭用。

 

三、帶有索引的表會由於插人記錄而導致分頁，但當刪除記錄後，頁會獲得釋放．從而形成跨幾個區的資料．而要訪問該資料就必須遍曆幾個區，這將導致增加I/O操作，查詢記錄的時間大大延長，開始時資料庫的效能雖然較高，但使用一段時間後就會發生效能下降等問題。實際上，資料在儲存空間上排列得越緊密有序，SQL Server訪問的速度就越抉，消除片段有助於提高系統的效能和更有效地利用資料存放區空間。(如例二，例二的訪問速度就比例一要快）。

 

四、對於掃描部分或全部表的查詢，這些表片段會導致額外的頁讀取，這將防礙資料的並行掃描。