SQL Server執行部分語句的記憶體代價

文章目錄

• 1.1    執行全表選取或者低選擇性選取
• 1.2    執行高選擇性選取
• 2.1    執行全表選取或者低選擇性選取
• 2.2    執行高選擇性選取
• 3.1    執行全表選取或者低選擇性選取
• 3.2    執行高選擇性選取
• 4.1    執行全表選取或者低選擇性選取
• 4.2    執行高選擇性選取
• 5.1    執行全表選取或者低選擇性選取
• 5.2    執行高選擇性選取
• 6.1    執行全表選取或者低選擇性選取
• 6.2    執行高選擇性選取

  眾所周知，SQL Server執行SQL語句的效能判定標準主要是IO讀取數大小。本文在不違反這一原則情況下，同時來分析一下部分SQL語句執行時，SQL Server記憶體的變化情況。

首先簡述一下SQL Server記憶體佔用的特點。SQL Server所佔用的記憶體除程式（即SQL Server引擎）外，主要包括緩衝的資料（Buffer）和執行計畫（Cache）。SQL Server以8KB大小的頁為單位儲存資料。這個和SQL Server資料在磁碟上的儲存頁大小相同。當SQL Server執行SQL 陳述式時，如果需要的資料已經在其記憶體中，則直接從記憶體緩衝區讀取並進行必要的運算然後輸出執行結果。如果資料還未在記憶體中，則首先將資料從磁碟上讀入記憶體Buffer中。而我們通常評價SQL效能指標中的IO邏輯讀取數對應的正是從記憶體緩衝區讀取的頁數，而IO物理讀取數則對應資料從磁碟讀取的頁數。

註：以下的實驗在多人共用的開發測試伺服器上也可以進行，因為實際上可以分別看到某個表所佔用的記憶體情況。但為了方便，筆者在做此實驗時，在一個單獨的、確認沒有其它並發任務的資料庫上進行，因此所看到的記憶體變化正是每一次所執行的SQL語句引起的。

我們首先來看一個簡單的執行個體。建立下表：

Create Table P_User

(   UserMobileStatus int NOT NULL,

    MobileNo int NOT NULL,

    LastOpTime DateTime Not NULL

)

然後為該表插入一定的資料：

Declare @i int

Set @i=28000

WHILE @i<29000

BEGIN

    Insert Into P_User

    Select @i % 2,@i,GetUTCDate()

    Set @i=@i+1

END

然後我們在查詢分析器中首先執行:

Set Statistics IO ON

並按下Ctrl+M以顯示實際的執行計畫。

此時，可以開始進行我們的實驗了。為了準確觀察每一次SQL語句變化情況，在執行第一條SQL語句以前，我們首先清空SQL Server所佔用的資料記憶體：

CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

這將清空SQL Server所佔用的資料緩衝區（此語句在生產伺服器上慎用，因為將導致一段時間內後續的SQL語句執行變慢）。

測試1：在沒有索引的表上執行SQL語句1.1    執行全表選取或者低選擇性選取

Select * From P_User

從SQL執行計畫可以看到，由於此時表中沒有任何索引，因此將產生Table Scan。而IO統計結果如下：

(1000 row(s) affected)

表'P_User'。掃描計數1，邏輯讀取4 次，物理讀取4 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

我們看一下資料庫記憶體中的情況。

首先查詢到我們所操作database的database_id：

Select database_id From sys.databases Where name='TestGDB'

然後使用該database_id從表中查看記憶體情況：

SELECT * FROM sys.dm_os_buffer_descriptors bd

WHERE database_id=5

order by allocation_unit_id,page_id

得到結果如下：

得到的結果中可以看到，除了必要的管理頁（一個PFS_Page和一個IAM_Page）外，記憶體中總共出現了4個Data_Page頁。這和剛才IO統計中看到的結果：邏輯讀為4，物理讀為4相同。由於是全表讀取，表明P_User表全部資料所佔用的資料頁數也正是4，將這4個資料頁的row_count數加起來也可以驗證其總資料行=1000。 

在上例中，如果不清空資料緩衝區，再執行一遍SQL，可以看到記憶體毫無變化，而邏輯讀也不變，只是物理讀變為0，因為已經不需要再從磁碟讀入資料。

1.2    執行高選擇性選取

另外，在沒有索引的情況下，如果將上例修改為：

Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28502

可以看到，系統同樣要讀取全部的資料頁到記憶體。

 如果使用Select Top 1 * From P_Order Where MobileNo=28502這樣的選取方式，有可能會出現唯讀取部分資料頁到記憶體的情況。但由於在沒有索引情況下，資料實際上是無序存放在堆上，所以結果很不穩定，也有可能發生讀取所有的資料頁到記憶體。

測試2：建立叢集索引情況下，執行SQL語句2.1    執行全表選取或者低選擇性選取

修改表結構，在MobileNo欄位上建立叢集索引。然後再次執行剛才的SQL語句。得到的執行計畫變為叢集索引掃描。IO統計訊息為：

(1000 row(s) affected)

表'P_User'。掃描計數1，邏輯讀取6 次，物理讀取1 次，預讀4 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

這裡的邏輯讀取變為6次。

記憶體情況如下：

記憶體中的變化是增加了一個非葉級的叢集索引頁，而葉級的叢集索引則會和資料放在一起。

另外，可以查看該表索引的層級：

SELECT database_id,object_id,index_id,index_level,page_count,record_count

FROM sys.dm_db_index_physical_stats

    (DB_ID(N'TestGDB'), OBJECT_ID(N'dbo.P_User'), NULL, NULL , 'DETAILED');

從結果可以看到該表的叢集索引總共分2級。

因而邏輯讀增加了2——（由於發生Clustered Index Scan，除了根層級的叢集索引頁佔用1次外，從根層級叢集索引定位到分葉層級的叢集索引也將額外佔用1次邏輯讀）。 

另外一個變化是只發生了一次物理讀，即讀取根層級的叢集索引頁，另外4個資料頁則通過預讀方式而不是物理讀從磁碟裝入記憶體Buffer。這使得有叢集索引的情況下，執行SQL所直接花費的代價實際上更小。

2.2    執行高選擇性選取

在建立叢集索引情況下，對效能有益的變化是：

對於Select Top 1 * From P_Order 或者Select * From P_Order Where MobileNo=28702這樣的語句，在有叢集索引情況下，只會將最終記錄所在的頁讀入記憶體。

測試3：建立非叢集索引情況下，執行SQL語句3.1    執行全表選取或者低選擇性選取

如果將表中同一欄位的叢集索引換成非叢集索引，則可以看到如下特點：

執行全表掃描將和沒有任何索引的情況相似，將讀取所有的資料頁到記憶體。此時,SQL Server的查詢引擎實際上無法使用非叢集索引。

3.2    執行高選擇性選取

將唯讀取最終資料所在的頁到記憶體。通過查詢計劃可以看到，SQL Server在非叢集索引上使用INDEX SEEK，然後通過lookup 得到資料實際所在行（索引覆蓋情況下例外，因為不需要定位到實際資料行）。

測試4：執行Nested Loop Join

在進行測試前,我們先準備另外一張表和資料。

Create Table P_Order

(   UserStatus int NOT NULL,

    MobileNo int NOT NULL,

    Sid int Not NULL,

    LastSubTime DateTime

)

插入資料：

Declare @i int

Set @i=20000

WHILE @i<30000

BEGIN

    Insert Into P_Order

    Select @i % 2,@i,@i-19999,GetUTCDate()

    set @i=@i+1

END

可以看到，在執行全表掃描情況下，該表10000條資料總共佔用38個記憶體資料頁。

4.1    執行全表選取或者低選擇性選取

Select * From P_Order A

Inner Loop JOIN P_User B ON A.MobileNo=B.MobileNo

對於此種高選擇性選擇，預設情況下SQL Server不會執行Loop Join。因此，使用了強制性的聯結提示。

在兩個表都沒有任何索引的情況下，可以看到：

兩個表所有的資料頁都將被載入到記憶體。邏輯讀取代價高達6萬多次——對於P_Order表中的每一條記錄，都將在P_User表中進行遍曆。

在其中一個表有叢集索引情況下，儘管邏輯讀取相比剛才的6萬多次已經大大下降，但仍然達到2萬次。而且聯結的次序對查詢效能影響很大。因為其實際執行的是將SQL語句中前面的表作為聯結的外部輸入，而後面的表作為聯結的內部輸入。

在兩個表都有叢集索引情況下，相比較而言，邏輯讀仍然達到數千次（取決於最終輸出的資料大小），但相比較已經大大改善。而且表中的資料只有最終需要輸出的部分才會被讀入記憶體Buffer中。

4.2    執行高選擇性選取

執行如下的SQL語句：

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

Where A.MobileNo=28913

在兩個表都沒有任何索引情況下，兩張表都將執行全表掃描。要讀入所有的資料頁到記憶體。總體邏輯讀取決於兩表的資料頁數。

在一個表有叢集索引或者非叢集索引情況下，該表將執行Index Seek，另一個表將出現全表掃描。記憶體資料緩衝區中，將有一張表唯讀入最終資料所在的資料頁、一張表讀入全部資料頁。邏輯讀數取決於表在聯結中的秩序、以及無索引表的資料頁數。

在兩個表都有叢集索引情況下，邏輯讀最小，每個表只有2到3次。而且只有實際需要輸出的資料才會被讀入記憶體頁。兩個表都有非叢集索引情況下，消耗的邏輯讀和記憶體資源近似。

測試5：執行Merge Join5.1    執行全表選取或者低選擇性選取

執行SQL:

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

如果兩張表都沒有任何索引，則兩張表都要進行全表掃描。所有的資料都要讀入記憶體頁。

邏輯讀數近似等於兩張表的資料頁總和。SQL Server處理過程中將使用到暫存資料表。

只有一張表有叢集索引的情形類似，SQL Server處理過程中將使用到暫存資料表。並且讀入所有的資料頁到記憶體。

如果兩張表都有叢集索引，儘管兩表的資料都會被讀入記憶體頁，但邏輯讀數已經大大減少，等於其中一張表總資料記憶體頁數加上最終輸出的資料頁數。而且SQL Server處理過程中將不需要再使用暫存資料表。

5.2    執行高選擇性選取

對於這樣的高選擇性SQL語句，SQL Server 將提示無法產生執行計畫。

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

Where A.MobileNo=28913

但可以執行：

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

Where A.MobileNo<=28001 （註：最終結果只有2條）

這樣的屬於低選擇性語句，但最終結果也很少的語句。如前面所述，這種情況下，採用netsted loop聯結效率可能更高。

測試6：執行Hash Join6.1    執行全表選取或者低選擇性選取

對於兩表聯結，如果兩張表都沒有索引，不寫明聯結提示的情況下，SQL Server預設使用hash join。而對於兩表聯結，如果兩張表都有叢集索引，則SQL Server預設使用Merge Join。

執行SQL:

Select * From P_Order A

Inner hash JOIN P_User B ON A.MobileNo=B.MobileNo

在使用hash join情況下，無論兩張表有無索引，都將讀取所有的資料頁到記憶體，SQL Server將使用暫存資料表進行處理。邏輯讀數近似等於兩張表的資料頁總和。

6.2    執行高選擇性選取

和merge join執行高選擇性選取情況類似，也無法直接執行：

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

Where A.MobileNo=28913 

但可以執行這樣的結果很少的低選擇性指令碼：

Select * From P_Order A

Inner merge JOIN P_User B ON A.MobileNo=B.MobileNo

Where A.MobileNo<=28001 （註：最終結果只有2條）

但此情況下，採用netsted loop聯結效率更高。

測試總結

本次測試的主要意義在於，通過分析具體的記憶體變化結合執行計畫、IO讀取等資訊，可以更清楚地瞭解SQL Server執行SQL 陳述式過程。

另外，也驗證了一些通過分析SQL 陳述式的IO讀取、執行計畫曾經得到的經驗：

（1）       在執行單表查詢時，如果是高選取查詢，要建立非叢集索引或者叢集索引（推薦非叢集索引，是獨立於資料存放的）。如果是低選擇性查詢，則需要建立叢集索引。

（2）       在執行聯結查詢時，如果最終輸出結果很少，則適宜使用nested loop join；如果輸出結果較多，則通過建立叢集索引，而以merge join方式查詢能得到好的效能。對於效能較低的hash join，最好通過轉換成merge join或者nested loop join方式提高查詢效能。