博客 / 詳情

返回

統一觀測丨使用 Prometheus 監控 SQL Server 最佳實踐

SQL Server 簡介

SQL Server 是什麼？

Microsoft SQL Server 是 Microsoft 推出的關係型數據庫解決方案，支持企業 IT 環境中的各種事務處理、商業智能和分析應用程序。Microsoft SQL Server 是市場領先的數據庫技術之一。

SQL Server 特點

  • 穩定：針對企業的應用需求，制定出適應環境的解決方案，保證了企業的數據安全和順利運行。
  • 易用：提供了豐富的圖形化管理工具，方便用户快速搭建數據庫系統。
  • 兼容：原生適配 Windows 系統，提供豐富的 API 訪問。
  • 性能：多種數據庫引擎優化算法，支持大量數據查詢存儲。

SQL Server 核心概念

圖片

關係引擎：關係引擎控制存儲引擎對數據的處理，並提供 SQL Server 組件來準確確定應如何執行查詢。關係引擎由三個主要部分組成。CMD 解析器（parser）主要負責識別和消除語義和語法錯誤，並生成查詢樹。優化器（Optimizer）通過消除冗餘任務和尋找最優計劃，確保所請求的查詢響應儘可能高效。查詢執行器（Query Executoe）會生成數據獲取邏輯的行為。

存儲引擎：當數據由存儲引擎存儲時，會從存儲系統（例如 SAN 或磁盤）檢索數據。存儲引擎中存在三種類型的文件：主文件、輔助文件和日誌文件。訪問方法（Access Method）負責在緩存管理器和事務日誌之間交換數據。緩存管理器（Buffer Manager）緩存當前的執行計劃和頁。事務管理器（Transaction Manager）使用日誌和鎖管理器對事務進行管理。

協議層：該層支持客户端-服務器架構以及流。協議層支持 3 種類型的客户端服務器架構：共享內存、TCP/IP、命名管道。

主要適用場景

Microsoft SQL Server 憑藉其可視化界面及其所具有的選項和工具，非常適合在關係數據庫中存儲所有所需的信息，以及輕鬆管理此類數據。

  • 事務處理：SQL Server 支持事務處理，通過使用事務，用户可以將一系列數據庫操作組合在一起，並確保它們要麼全部成功執行，要麼全部回滾到初始狀態。這對於處理銀行交易、在線購物和庫存管理等需要保證數據一致性的應用程序非常關鍵。
  • 通過大數據集羣對所有數據進行智能分析：SQL Server 提供了強大的數據倉庫和商業智能功能。用户可以使用 SSIS 將數據從不同的數據源中提取、轉換和加載到數據倉庫中。然後，您可以使用 SSAS 創建多維數據模型和立方體，以支持複雜的數據分析和報表需求。此外，SQL Server還提供了數據挖掘和預測分析功能，幫助組織發現數據中隱藏的模式和趨勢。
  • 可擴展性：SQL Server 提供了廣泛的開發和編程功能，以支持應用程序開發人員。SQL Server 還支持水平和垂直擴展，用户可以在需要時增加服務器硬件資源或在多個服務器之間進行數據分區和分佈，以處理大規模數據和高併發負載。SQL Server 允許用户輕鬆地將數據庫管理系統與任何設備和 Azure 服務集成，以獲得更好的數據性能和分析能力。

主要版本介紹

SQL Server 2022：安全、性能、可用性等加強；查詢存儲和智能查詢處理

SQL Server 2019：數據虛擬化和大數據羣集；智能數據庫、智能查詢；內存數據庫

SQL Server 2017：圖形數據庫功能、動態管理視圖、內存優化等

SQL Server 2016：內存OLTP、Stretch Database、集成 Hadoop 等

監控關鍵指標

這裏介紹監控 SQL Server 服務中常見的關鍵指標。

圖片

系統指標

運行狀態

啓動狀態是監控 SQL Server 最基礎的指標，表示 SQL Server 實例是否在正常運行，或是否重啓。SQL Server 重啓時，沒有 commit 的數據會丟失，小概率產生錯誤。

版本/實例時間

監控啓動的 SQL Server 實例是否符合預期，是否是業務要求的 SQL Server 版本。保證 SQL Server 的本地時間與客户端保持一致，否則數據庫返回的時間有可能發生錯誤。

讀寫指標

頁指標

頁讀寫：頁是 SQL Server 存儲引擎磁盤管理的最小單位，為數據庫中的數據文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁（從 0 到 n 連續編號）。磁盤 I/O 操作在頁級執行。也就是説，SQL Server 讀取或寫入所有數據頁。因此對頁的讀寫監控尤為重要。通過讀寫頁數量的指標，可以計算出頁讀寫的速率，從而判斷 SQL Server 的執行性能。

頁在緩存的停留時間：所有數據庫軟件的主要設計目標之一是儘量減少磁盤 I/O，因為磁盤的讀取和寫入操作佔用大量資源。SQL Server 在內存中生成緩存池，用於保存從數據庫讀取的頁。我們需要監控頁在緩存池的生命時長，頁在緩存池中存在的時間越長，表示其被命中的可能性越大，也就是説訪問該頁時不需要訪問磁盤。

惰性寫（lazy write）：在緩衝區緩存中修改頁後，不會將其立即寫回磁盤；而是將其標記為“髒”。也就是説在將頁物理寫入磁盤之前，可以將其邏輯寫入多次。在正常運行的情況下，髒頁定期地刷入磁盤。而當不斷地有新數據寫入 SQL Server 且緩存不夠用的情況下，大量的髒頁會被移出緩存。

檢查點（checkpoint）：當檢查點發生時，SQL Server 要求刷新所有髒頁至磁盤，此時 SQL Server 性能受到一定影響。我們需要監控檢查點的速度，確保檢查點的刷新速度達到預期。

頁錯誤（page fault）：當頁錯誤發生時，表示需要的頁在 SQL Server 可管理的內存區域之外。當遇到頁面錯誤時，程序執行停止並設置為等待狀態。操作系統在磁盤上搜索所請求的地址。當找到該頁面後，操作系統將其從磁盤複製到空閒 RAM 頁面中。操作系統允許程序隨後繼續執行。

日誌成長次數

SQL Server 數據庫引擎為數據庫中的每個操作寫入日誌記錄，其中包括執行數據修改過程時、創建或刪除數據庫表或索引時以及每次分配或刪除頁面後開始或結束 SQL 事務。日誌助於在系統或硬件發生故障時將數據庫恢復到特定時間點。在具有過多日誌記錄操作的事務性很強的系統中，SQL Server 事務日誌文件將快速增長，直到達到其最大大小，從而生成錯誤號 9002。如果啓用自動增長選項，底層磁盤驅動器將耗盡可用空間。

I/O 等待（stall）時間

I/O 等待時間是一個可用於檢測 I/O 問題的指標。SQL Server 將數據寫入文件或是從文件讀數據時，都需要等待較長的時間，表示為 I/O 等待時間，停頓時間較長表明存在 I/O 問題和磁盤活動繁忙。文件 I/O 屬於數據庫的關鍵路徑，等待的時間直接反應到客户端讀寫 SQL Server 的延遲。每個數據庫存儲的文件不同，使用的存儲介質也可能不同。因此除了監控整體的 I/O 等待時間，還需要監控各個數據庫的 I/O 等待時間，以便運維做出針對性的優化。

連接指標

在 SQL Server 中，執行查詢都依賴於建立和維護客户端連接。當需要維護 SQL Server 的可用性和高性能，監控連接是運維工作的良好入口。當 SQL Server 併發連接數過多可能會使服務器超載。當連接成功建立，不管是否使用連接，每個連接都會產生開銷。

存儲指標

如上文所述，儘管 SQL Server 將數據存儲在磁盤中，但因為緩存池的存在，對 SQL Server 內存使用情況同樣要重點關注。默認情況下，SQL Server 根據可用的系統資源動態管理其內存需求。如果 SQL Server 需要更多內存，它會查詢操作系統以確定是否有可用的空閒物理內存並使用可用內存。如果操作系統的可用內存不足，SQL Server 會將內存釋放回操作系統，直到內存不足的情況得到緩解，或者直到 SQL Server 達到最小服務器內存限制。

性能指標

事務處理速率（TPS）

一個事務是指一個客户端向 SQL Server 發送請求然後 SQL Server 做出反應的過程。客户端在發送查詢請求時開始計時，收到 SQL Server 響應後結束計時，以此來計算使用的時間和完成的事務個數。一般的，評價 SQL Server 性能均以每秒鐘完成的客户端請求的數量來衡量。

批處理（T-SQL）速率

T-SQL (Transact-SQL) 是 Sybase 和 Microsoft 的一組編程擴展，它為結構化查詢語言 ( SQL ) 添加了多種功能，包括事務控制、異常和錯誤處理、行處理和聲明的變量。所有與 SQL Server通信的應用程序都是通過向服務器發送 T-SQL 語句來實現的。

指標詳細定義

系統指標

圖片

讀寫指標

圖片

連接指標

圖片

存儲指標

圖片

性能指標

圖片

監控大盤

我們默認提供了 SQL Server Overview 大盤。

總覽

在該 panel 能看到 SQL Server 運行時需要重點關注的指標，在檢查 SQL Server 狀態時，首先查看總覽中是否有異常狀態，再檢查具體的指標。

  • 啓動狀態：綠色代表正常運行，紅色代表異常運行
  • 內存使用率：使用紅黃綠顏色提示，內存使用率在 80% 以下時為綠色，80%～90% 為黃色，90% 以上為紅色

圖片

資源

內存是 SQL Server 的重點關注硬件資源，通過該 panel 能瞭解 SQL Server 的內存使用情況：

  • 最大內存：提供內存整體狀態
  • 內存使用率/使用量：分析內存使用的趨勢
  • 文件擴展次數：分析操作數據量的趨勢
  • 數據庫文件大小：查看數據庫存儲的數據量趨勢，以及日誌的存儲量的趨勢

圖片

圖片

性能

在以下 panel 能看到 SQL Server 的運行效率，分為以下三類：

  • 事務處理速率：表示 SQL Server 每秒處理的事務數量，直接影響客户端查詢請求的延遲
  • 批處理速率：表示 SQL Server 每秒能處理多少個 Transact-SQL
  • 死鎖次數：檢測是否發生了因為數據庫競爭而發生的死鎖，嚴重影響性能

圖片

讀寫

在以下 panel 能看到 SQL Server 的運行效率：

  • 讀寫頁速率：監控讀寫頁的速率趨勢，是否達到預期速率，是否發生波峯波谷等
  • 惰性寫速率：定期輸入髒頁的速率趨勢，監控是否平穩
  • page 生命預期：page 的生命預期越高越好
  • I/O 等待時間：SQL Server 等待讀寫文件的等待時間趨勢
  • 檢查點速率：當發生檢查點，需要監控運行速率

圖片

數據庫 I/O Stall

除了監控 SQL Server 的整體 I/O 等待時間，還需要分別監控數據庫的 I/O 等待時間。每個數據庫存儲的文件不同，存儲的磁盤可能不同，需要做出針對性優化方案。

圖片

連接

需要隨時檢查 SQL Server 的連接情況，防止過高的併發影響性能。

  • 總連接數：監控整體連接數的趨勢
  • 連接錯誤數：監控是否有客户端發生了連接錯誤，保證系統正常運行
  • 各數據庫的連接用户：分別監控每個數據庫連接的客户端，以及每個客户端發起的連接數量

圖片

數據庫狀態

監控各數據庫是否正常運行，下表定義了數據庫的狀態。

圖片

關鍵告警規則

在對 SQL Server 進行告警規則配置時，我們推薦基於以上採集得到的指標，從以下幾個方面進行告警規則的配置，分別是運行情況、資源使用情況、連接使用情況。一般來説，我們默認生成影響 SQL Server 正常使用的告警規則，優先級較高。讀寫速率等與業務相關的告警則由用户自定義。以下是一些推薦的告警規則。

運行情況

SQL Server 停機

SQL Server 停機是 0/1 閾值的告警規則。一般來説，部署在 ACK 等阿里雲環境的 SQL Server 服務具有高可用的能力，當一個 SQL Server 實例停止，其他的實例會繼續工作。本報警引發的原因可能是所有的 SQL Server 都無法正常啓動，或者 Exporter 錯誤無法獲取數據。我們默認設定5分鐘內 SQL Server 無法恢復的告警。

SQL Server 重啓

SQL Server 重啓是 0/1 閾值的告警規則絕大部分情況下 SQL Server 因為有日誌的存在，不存在數據丟失的情況。但 SQL Server 重啓之後緩存池的內容被清空，造成暫時的查詢緩慢。正在執行的事務要回退給客户端，引發一系列暫時的錯誤，需要客户端重新發起請求。

資源使用情況

內存使用率過高

SQL Server 對服務器內存的使用策略是用多少內存就佔用多少內存，不加限制的話有可能把節點的所有內存資源佔用。當內存使用率過高，SQL Server 無法正常運行。我們設定的內存使用閾值為：危險值 80%，告警值 90%。當內存使用率為 80% 時，節點高負荷運轉，但一般不影響正常使用。當內存長時間使用率為 90% 時，將發出告警，提示運維資源緊缺，儘早處理。

發生死鎖

SQL Server 發生死鎖是 0/1 閾值的告警規則，不考慮死鎖的個數。一系統中遇到的死鎖數通常很少，而一旦發生死鎖，需要終止被線程執行的當前批處理，回滾死鎖犧牲品的事務，回退給客户端一個錯誤信息。

連接使用情況

發生連接錯誤

SQL Server 發生連接錯誤是 0/1 閾值的告警規則。發生該錯誤可能有很多原因，如：遠程主機強制關閉現有連接、超時過期。操作完成之前已過的超時期限或服務器未響應、無法生成 SSPI 上下文等，需要登錄數據庫並檢查日誌查看錯誤原因。

典型問題場景及其排查/解決方法

SQL Server 性能差

SQL Server 性能差體現在事務處理速率（TPS）、批處理（T-SQL）速率低的指標上， 性能差有許多原因可能導致，我們需要聯繫多個指標進行排查。

檢查內存使用率
  • 原因：內存不足的情況下，緩存池無法緩存所有的熱點數據，導致多次數據訪問請求發送到磁盤
  • 排查方法：檢查大盤中的內存使用率 panel，檢查內存使用率是否一直都很高。檢查告警歷史，查看是否提示內存資源不足。
  • 解決方法：優化對應節點的磁盤資源；
檢查 I/O 等待時間
  • 原因：I/O 等待時間較長表明存在 I/O 問題和磁盤活動繁忙。
  • 排查方法：檢查大盤中的 I/O 等待時間 panel，檢查 I/O 等待時間是否一直都很高。檢查告警歷史，查看是否提示內存資源不足。
  • 解決方法：可能是出現大量的波峯訪問，突然增大磁盤訪問，考慮優化 SQL Server 架構；進一步檢查節點的文件系統是否有 I/O 問題；優化對應節點的磁盤資源；
檢查日誌增長次數
  • 原因：當數據庫中執行任何修改時，SQL Server 會將修改寫入日誌緩衝區，然後將該緩衝區數據寫入磁盤。當寫入數據過多，日誌內容來不及刷入磁盤。
  • 排查方法：檢查大盤中日誌增長次數的 panel，檢查是否有某段時間日誌增長次數突然升高。
  • 解決方法：為日誌文件選擇的磁盤必須在順序讀寫吞吐量和最小延遲方面表現良好。

檢查檢查點（checkpoint）

  • 原因：執行檢查點時，SQL Server 將內存中的所有髒頁刷新到磁盤，會影響整體數據庫性能，給磁盤帶來較大壓力。
  • 排查方法：查看檢查點 panel，查看是否有檢查點速率的數據，表示在該時間段進行了檢查點操作。
  • 解決方法：設計進行檢查點的時機，儘量避開高峯時間段。

監控體系搭建

自建 Prometheus 監控 SQL Server 的痛點

通常我們當前的 SQL Server 都是部署在 ECS 上，因此自建 Prometheus 監控 SQL Server 時，我們將面臨的典型問題有：

  1. 由於安全、組織管理等因素，用户業務通常部署在多個相互隔離的 VPC，需要在多個 VPC 內都重複、獨立部署 Prometheus，導致部署和運維成本高。
  2. 每套完整的自建監控系統都需要安裝並配置 Prometheus、Grafana、AlertManager 等，過程複雜、實施週期長。
  3. 缺少與阿里雲 ECS 無縫集成的服務發現（ServiceDiscovery）機制，無法根據 ECS 標籤來靈活定義抓取 targets。如果自行實現類似功能，則需要使用 Golang 語言開發代碼（調用阿里雲 ECS POP 接口）、集成進開源 Prometheus 代碼、編譯打包後部署，實現門檻高、過程複雜、版本升級困難。
  4. 常用開源 Grafana SQL Server 大盤不夠專業，缺少結合 SQL Server 原理/特徵和最佳實踐進行深入優化。
  5. 缺少 SQL Server 告警指標模板，需要用户自行研究、配置告警項，工作量大。

用阿里雲 Prometheus 進行自建 SQL Server 的監控

  • 登錄 ARMS 控制枱[1]。
  • 在左側導航欄選擇 Prometheus 監控 > Prometheus 實例列表，進入可觀測監控 Prometheus 版的實例列表頁面。
  • 單擊目標 Prometheus 實例名稱，進入集成中心頁面。
  • 單擊 SQL Server 卡片的安裝

圖片

  • 配置相關參數，並單擊確定，完成組件接入。

圖片

已接入的組件會顯示在集成中心頁面的已安裝區域。單擊該組件卡片，在彈出的面板中可以查看 Targets、指標、大盤、告警、服務發現配置、Exporter 等信息。

圖片

如下圖所示，您可以看到目前可觀測監控 Prometheus 版提供的關鍵告警指標。

圖片

您可以在大盤頁籤，單擊大盤縮略圖，查看對應 Grafana 大盤。

圖片

您可以面板中單擊告警頁籤，查看 SQL Server 的 Prometheus 告警。您還可以根據業務需求新增告警規則。創建 Prometheus 告警規則的具體操作，請參見 Prometheus 告警規則[2]。

圖片

自建 Prometheus 與阿里雲可觀測監控 Prometheus 版監控 SQL Server 優劣對比

圖片

可觀測監控 Prometheus 版針對產品新用户，提供三個月每日 2000w 自定義指標上報額度，點擊閲讀原文立即免費試用。

參考鏈接：

[1] https://learn.microsoft.com/zh-cn/sql/sql-server/what-s-new-i...

[2] https://www.sqlshack.com/sql-server-troubleshooting-disk-i-o-...

[3] https://learn.microsoft.com/en-us/sql/relational-databases/pe...

[4] https://learn.microsoft.com/zh-cn/sql/relational-databases/pa...

[5] https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-e...

[6] https://learn.microsoft.com/zh-cn/sql/relational-databases/me...

[7] https://www.sqlshack.com/sql-server-memory-performance-metric...

[8] https://www.sqlshack.com/sql-server-transaction-log-growth-mo...

[9] https://blog.csdn.net/Superman7658/article/details/130799559

[10] https://learn.microsoft.com/zh-cn/sql/relational-databases/da...

相關鏈接：

[1] ARMS 控制枱

https://account.aliyun.com/login/login.htm?oauth_callback=htt...

[2] Prometheus 告警規則

https://help.aliyun.com/zh/arms/prometheus-monitoring/create-...

作者：啃唯

點擊立即免費試用雲產品 開啓雲上實踐之旅！

原文鏈接

本文為阿里雲原創內容，未經允許不得轉載。

阿里雲 , server , prometheus , 雲原生 , SQL
user avatar thehumble 頭像 hexuan_5ea1a9d57db1a 頭像 kuanrongdebeizi 頭像
3 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.