tempdb大量閂鎖等待問題分析

z_cloud_for_SQL發表於2023-02-24

現象 1

登入到SQL專家雲中,進入實時視覺化頁面,在活動會話裡面看到有大量資源等待的會話。

點選一個時間點,進入到該時間點的活動會話原始資料。看到大量會話的等待型別為 PAGELATCH_UP,等待資源為 “2:1:xxxxxxx” , SQL語句都和臨時表有關。

分析 2

會話等待的資源“2 1:xxxxxxx ” 代表 ID 2 的資料庫( tempdb)1號檔案(tempdev)的 xxxx xxx頁。SQL語句建立一個臨時表時,相當於在tempdb中建立一張表 SQL Server要為這張表分配儲存頁面,需要修改 SGAM、PFS、 GAM系統資料頁,為了 其他表不會分配到同一個資料頁,在修改時使用閂鎖,修改完成後釋放閂鎖。

這種機制對一般的使用者資料庫不會有問題,因為正常的應用不會折騰著不停地建表、刪表。 但是 tempdb 就不同了,經常會有高併發的SQL語句使用臨時表 。因此 在同一個時間點會有很多執行緒要修改系統頁,就會產生大量的 PAGELATCH_U P閂鎖等待。


解決 3

 最直接的解決辦法是增加tempdb資料檔案的個數,這樣多個執行緒修改系統頁時就被分配到不同的資料檔案上,從而降低了閂鎖爭用。在增加資料檔案時有2點重要的注意事項:

  1. 檔案個數一定要和CPU核數對齊,也就是能被CPU核數整除。例如是40個核,則可以建立4個、8個、10個、20個等。建議最少4個, 最多 以和CP U 的核 數一 樣多,但 一般 的服 務器CPU核數都在幾十 ,沒有必要建立這麼多

  2. 每個資料檔案的初始大小和增長大小的配置一定要一致,如果不一致,SQL Server將不會平均地使用每個資料檔案,而是先使用容量最大的資料檔案。

tempdb的重要性 4

 臨時表、表變數、查詢語句(ORDER BY、GROUP BY、UNION等)、觸發器、索引維護、快照事務隔離級別等很多功能都會用到tempdb,詳細參考微軟官方文件

最佳實踐 5

對tempdb依賴 較高的實 例,可以 te mpdb 單獨 一個快 速的 物理 盤,例如SSD ,讓tempdb獨享I/O資源, 使用者資料 爭搶,從而加快tempdb的響應速度 。如果伺服器有基於磁碟映象的容災軟體,為tempdb規劃單獨的磁碟的另一個好處是不用把temdp檔案同步到備機。


北京格瑞趨勢科技有限公司是聚焦於資料服務的高新技術企業,成立於2008年,創始團隊及核心技術人員來自微軟和雅虎。微軟資料平臺金牌合作伙伴,衛寧健康資料平臺戰略合作伙伴。透過產品+服務雙輪驅動的業務模式,14年間累計服務4000+客戶,覆蓋網際網路、市政、交通、電信、醫療、教育、電力、製造業等各個領域。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70023658/viewspace-2936804/,如需轉載,請註明出處,否則將追究法律責任。

相關文章