SQL Server
盡可能減緩 SQL Server 中的封鎖情形
Cherié Warren
摘要:
- 為什麼會發生鎖定擴大
- 避免不必要的封鎖
- 查詢最佳化
- 監視鎖定對效能的影響
要支援同時讀寫資料庫，肯定少不了鎖定，雖說如此，封鎖也可能會拖累系統效能，不過有時候還很難察覺得到。在本文中，我將探討如何將 SQL Server 2005 或 SQL Server 2008 資料庫最佳化，
以便將封鎖的情形減至最低，還會討論如何監視系統，讓您更加了解鎖定對效能的影響。
鎖定和擴大
SQL Server® 會根據有多少記錄會受到影響，還有系統上同時存在什麼活動來選擇適當程度的鎖定。在預設的情況下，SQL Server 會盡可能選擇最細微的鎖定，只有在更能有效運用系統記憶體的情況下，才會選擇較廣泛的鎖定。如果擴大可提高整體系統效能，SQL Server 就會擴大鎖定。如 [圖 1] 所示，當特定掃描中的鎖定數目超過 5,000，或是系統用於鎖定的記憶體超過可用的容量時，就會發生擴大的情形。
Figure 1** 導致鎖定擴大的條件 **(按影像可放大)
- 如果鎖定設定為 0，資料庫引擎會使用 24% 的非 Address Windowing Extensions (非 AWE) 記憶體
- 如果鎖定設定不是 0，資料庫引擎會使用 40% 的非 AWE 記憶體
發生擴大的，一定是資料表鎖定。
避免不必要的封鎖
任何程度的鎖定都可能會發生封鎖，但若發生擴大，封鎖範圍就會增大。鎖定擴大可能是應用程式設計、編寫或設定不良的徵兆。
若要預防封鎖，一定要遵守資料庫設計基礎 (例如透過縮小金鑰使用正規化結構描述，以及避免在交易系統上進行大量資料作業)。若不遵守這些準則 (例如將報告系統與交易系統相區隔，或是在非營業時間處理資料餵送)，將很難微調系統。
若要知道存取資料需要多少鎖定，編製索引可說是關鍵的要素。索引會減少資料庫引擎必須執行的內部查詢次數，藉此減少查詢存取的記錄數量。譬如，當您從資料表的非索引資料行上選取單一資料列時，資料表中的每一個資料列都必須暫時加以鎖定，直到找出所需的記錄為止。相反地，如果該資料行已經編製索引，就只需要一個鎖定。
SQL Server 2005 和 SQL Server 2008 兩者都包含動態管理檢視 (sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_groups、sys.dm_db_missing_index_details)，它們會根據累計的使用統計資料，顯示被索引嘉惠的資料表和資料行。
分散情形也可能暗喻著效能問題，因為資料庫引擎可能需要存取比沒有分散情形時更多的頁面。另外，錯誤的統計資料也可能使查詢最佳化工具選擇比較沒有效率的計畫。
請記住，雖然索引可以加速資料存取，但也可能減緩資料的修改作業，因為不僅基本資料需要變更，連索引也需要更新。動態管理檢視 sys.dm_db_index_usage_stats 特別說明了索引使用的頻率。低效率索引最常發生在複合索引身上，在這裡無論隔離或複合都會編製相同的資料行。由於 SQL Server 是從左到右存取索引，只要最左邊的資料行可用，就會使用索引。
分割資料表不但可將系統最佳化 (縮小封鎖的範圍)，也可將資料分成不同的實體物件以供個別競用。雖然分隔資料比較明顯的作法是啟用資料列磁碟分割，但是您也可以考慮水平分割資料。您可以特意選擇取消正規化，方法是將資料表分割成含有相同資料列和金鑰數目但資料行不同的個別資料表，藉此降低不同處理序同時獨佔存取資料的可能性。
應用程式可存取特定資料列之資料的方法越多元化，該資料列裡面可包含的資料行就越多，而資料行分割的方法也越吸引人。這種方法有時候也可以嘉惠應用程式佇列和狀態資料表。SQL Server 2008 新增了依磁碟分割 (若資料表未啟用磁碟分割的話，可依資料表) 停用鎖定擴大的功能。
查詢最佳化
查詢最佳化在強化效能方面扮演了很重要的角色。下面是您可採取的三種方法：
縮短交易 減少封鎖以及提升整體效能最重要的方法之一，就是盡量縮小交易的規模。任何對交易完整性不重要的處理作業 (例如查詢相關資料、編製索引，以及拖曳資料) 都應該去除以縮小交易。
SQL 會將每個陳述式視作隱含交易。如果陳述式會影響大量資料列，即使是一個陳述式也可以構成大型交易，尤其若是牽涉到許多資料行或是資料行包含大型資料類型的話更是如此。如果填滿因素偏高，或是 UPDATE 陳述式以大於之前配置的值來填滿資料行的話，單一陳述式也可能會導致頁面分割。在這種情況下，比較實用的作法是將交易分成資料列群組，一次處理一個，直到全部完成為止。您應該只在個別陳述式或一組陳述式可分成較小型的批次，而且這些小型批次無論成功或失敗都能被視為完整的工作單位時，才考慮採用批次處理。
排列交易順序 在交易內部特意排列陳述式的順序，可以降低封鎖的可能性。您必須牢記兩個準則。第一，在系統內所有的 SQL 程式碼中，務必以相同的順序存取物件。如果順序不一致，兩個競爭的處理序就會以不同的順序存取資料，這種情形可能會產生死結，進而導致其中一個處理序發生系統錯誤。第二，務必將最常存取或耗用最多資源存取的物件，置於交易結尾。SQL 會在交易中等到必要時才鎖定物件。延遲對「作用點」的存取，可縮短這些物件保有鎖定的時間。
使用鎖定提示 您可以針對特定資料表或檢視，在工作階段層級或陳述式層級使用鎖定提示。使用工作階段層級提示的典型案例，是資料倉儲所用的批次處理，在這項作業中，開發人員知道該處理序是在特定時間內在該資料集合上唯一執行中的處理序。SQL Server 不會保留任何讀取鎖定 (方法是在預存程序開始時使用諸如 SET ISOLATION LEVEL READ UNCOMMITTED 等命令)，因此也減少整體的鎖定負荷並提升效能。
當開發人員知道可以安全的進行中途讀取時 (例如，從資料表讀取其他並行處理序永遠都不需要的資料列)，或是當所有其他效能微調皆告失敗時 (結構描述設計、索引設計和維護，以及查詢微調)，以及開發人員想要強制編譯器使用特定種類的提示時，都是使用陳述式層級提示的典型案例。
如果監視指出在查詢幾乎影響不到記錄的地方發生較廣泛的鎖定，這時候使用資料列鎖定提示比較理想，因為這樣可以減少封鎖的情形。如果監視指出幾乎資料表中所有的記錄都受到查詢影響時，發生較細微的鎖定，這時候使用資料表鎖定提示比較適合，因為這樣可以減少保持鎖定所需的系統支援。請注意，指定鎖定提示並不保證在鎖定數目到達系統記憶體閾值時鎖定不會擴大。不過，這麼做倒可以防止其他所有的擴大情況。
調整組態
如 [圖 2] 所示，設定 SQL Server 系統時有幾項因素需要考慮。
Figure 2** SQL Server 如何判斷可用於鎖定的記憶體量 **(按影像可放大)
記憶體 鎖定一定是保存在非 AWE 記憶體中，因此擴大非 AWE 記憶體，可以增加系統容納鎖定的容量。
如果想要增加鎖定容量，應先考慮 64 位元架構，因為 32 位元架構只能使用 4GB 的非 AWE 記憶體，而 64 位元則完全沒有限制。
在 32 位元的系統上，您可以在 Boot.ini 檔案中加入 /3GB 參數，向作業系統多抓取 1GB 的記憶體給 SQL Server 用。
SQL Server 組態設定 您可以透過會影響鎖定的 sp_configure，調整各種設定。鎖定設定會設定系統在擲出錯誤之前可保有多少鎖定。根據預設，這項設定值為 0，也就是說，伺服器會與其他競用記憶體的處理序動態調整保留的鎖定數目。SQL 最先會保留 2,500 個鎖定，而且每個鎖定會耗用 96 個位元組的記憶體，不會用到分頁記憶體。
最小和最大記憶體設定會保留 SQL Server 所用的記憶體量，因而設定讓伺服器靜態保有記憶體。由於鎖定擴大與可用記憶體有關，因此競用處理序所保留的記憶體量，對於是否會發生擴大有著深遠的影響。
連線設定值 在預設的情況下，封鎖的鎖定並不會逾時，但是您可以使用 @@LOCK_TIMEOUT 設定，在超過釋放鎖定的指定等候閾值時產生錯誤。
追蹤旗標 鎖定擴大與兩個追蹤旗標特別息息相關。一個是追蹤旗標 1211，它會停用鎖定擴大。如果取用的鎖定數目超過可用的記憶體，就會發生錯誤。另一個是追蹤旗標 1224，它會停用個別陳述式的鎖定擴大。
觀察系統
深入閱讀
- 《Inside Microsoft SQL Server 2005:The Storage Engine》，Kalen Delaney 著
- 如何監視 SQL Server 2005 與 SQL Server 2000 中的封鎖情形
- SQL Server 2005 效能統計資料指令碼
- 如何解決因 SQL Server 封鎖擴大所造成的封鎖問題
- 鎖定擴大 (資料庫引擎)
您可以針對封鎖和鎖定，監視鎖定對整體系統效能的衝擊，方法是在指定間隔 (可能是每小時) 輪詢狀態資料，以及擷取保有鎖定的執行統計資料。您要擷取的重要資訊有：
- 受影響的物件、鎖定程度和鎖定類型
- 鎖定和封鎖持續的時間
- 發出的 SQL 命令 (預存程序名稱、所含的 SQL 陳述式)
- 相關封鎖鏈的資訊
- 系統如何取用它的可用鎖定容量
您可以執行如 [圖 3] 所示的指令碼來擷取這項資訊，將它寫入含相關時間戳記的資料表中。若要進一步分析遭到封鎖的資料的 ResourceId，可以執行像 [圖 4] 所示的指令碼。
Figure 4 Learning more about blocked data
DECLARE @SQL nvarchar(max)
, @CallingResourceType varchar(30)
, @Objectname sysname
, @DBName sysname
, @resource_associated_entity_id int
-- TODO: Set the variables for the object you wish to look up
SET @SQL = N'
USE ' + @DbName + N'
DECLARE @ObjectId int
SELECT @ObjectId = CASE
WHEN @CallingResourceType = ''OBJECT''
THEN @resource_associated_entity_id
WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = @resource_associated_entity_id)
WHEN @CallingResourceType = ''ALLOCATION_UNIT''
THEN (SELECT CASE
WHEN type IN (1, 3)
THEN (SELECT object_id
FROM sys.partitions
WHERE hobt_id = allocation_unit_id)
WHEN type = 2
THEN (SELECT object_id
FROM sys.partitions
WHERE partition_id = allocation_unit_id)
ELSE NULL
END
FROM sys.allocation_units
WHERE allocation_unit_id = @resource_associated_entity_id)
ELSE NULL
END
SELECT @ObjectName = OBJECT_NAME(@ObjectId)'
EXEC dbo.sp_executeSQL
@SQL
, N'@CallingResourceType varchar(30)
, @resource_associated_entity_id int
, @ObjectName sysname OUTPUT'
, @resource_associated_entity_id = @resource_associated_entity_id
, @CallingResourceType = @CallingResourceType
, @ObjectName = @ObjectName OUTPUT
Figure 3 Capturing locking stats
SELECT er.wait_time AS WaitMSQty
, er.session_id AS CallingSpId
, LEFT(nt_user_name, 30) AS CallingUserName
, LEFT(ces.program_name, 40) AS CallingProgramName
, er.blocking_session_id AS BlockingSpId
, DB_NAME(er.database_id) AS DbName
, CAST(csql.text AS varchar(255)) AS CallingSQL
, clck.CallingResourceId
, clck.CallingResourceType
, clck.CallingRequestMode
, CAST(bsql.text AS varchar(255)) AS BlockingSQL
, blck.BlockingResourceType
, blck.BlockingRequestMode
FROM master.sys.dm_exec_requests er WITH (NOLOCK)
JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
ON er.session_id = ces.session_id
CROSS APPLY fn_get_sql (er.sql_handle) csql
JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
SELECT cl.request_session_id AS CallingSpId
, MIN(cl.resource_associated_entity_id) AS CallingResourceId
, MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType
, MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode
-- (i.e. schema, update, etc.)
FROM master.sys.dm_tran_locks cl WITH (nolock)
WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting
GROUP BY cl.request_session_id
) AS clck
ON er.session_id = clck.CallingSpid
JOIN (
-- Retrieve lock information for blocking process
-- Only one record will be returned (one possibility, for instance,
-- is for multiple row locks to occur)
SELECT bl.request_session_id AS BlockingSpId
, bl.resource_associated_entity_id AS BlockingResourceId
, MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType
, MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode
FROM master.sys.dm_tran_locks bl WITH (nolock)
GROUP BY bl.request_session_id
, bl.resource_associated_entity_id
) AS blck
ON er.blocking_session_id = blck.BlockingSpId
AND clck.CallingResourceId = blck.BlockingResourceId
JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
ON er.blocking_session_id = ber.session_id
CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE ces.is_user_process = 1
AND er.wait_time > 0
您也可以透過 SQL Profiler (Lock:Escalation event)、dm_db_index_operational_stats 動態管理檢視 (index_lock_promotion_count) 或定期輪詢系統鎖定資訊，來監視系統的擴大情形。從監視擴大所收集的相關資訊是處理作業是否核准擴大，如果不核准，相關的預存程序是否可以找出效能問題的根源。含大量資料或高度並行使用量的資料表，應該是擴大的主要重點。
收集了鎖定、封鎖和擴大的相關資料後，接著可以分析該資料，判斷每個物件的累加封鎖和鎖定時間 (事件的數量乘以事件的持續時間)。通常這就是部署、監視、分析和修正變更的重複效能調整週期的開端。有時候只需要進行簡單的變更 (例如新增索引以大幅提升效能)，就可以更改系統中最頭痛的效能瓶頸區。
有關減少 SQL Server 封鎖的詳細資訊，請參閱「深入閱讀」資訊看板。如果能夠在設計、編寫程式碼和穩定化階段當中，特別注意不讓交易規模擴大，許多封鎖問題便能迎刃而解。適當的硬體也能大幅降低發生不必要擴大的可能性。無論如何，只要持續評估系統上的封鎖情形，就可以迅速找出效能問題的根源。
Cherié Warren 擔任 Microsoft IT 的資深開發主任一職，她目前負責 Microsoft 內部最大的交易資料庫之一。Cherié 也經常追根究底，以及解決封鎖相關的效能問題。她專事企業層級 SQL Server 資料庫已有 10 年的時間。
© 2008 Microsoft Corporation and CMP Media, LLC. 保留所有權利；未經允許，嚴禁部分或全部複製.