CaryHsu - 學無止盡: 如何進行 SQL Server 效能資料收集與分析

2023年12月11日星期一

如何進行 SQL Server 效能資料收集與分析 - 以 LogScout, PSSDiag, SQL Nexus 工具為例

前面的文章，有介紹過該如何透過 TroubleShootingScript toolset (TSS) 收集系統資訊，這個通常主要用於分析如資料庫為何 Failover，意外重啟等情況，但如果要進行效能分析時，該如何進行，因為很多時候效能的問題無法立即重現與立即觀察，所以本篇要來特別介紹該如何來收集效能資訊與收集到的資訊後，該如何進行工具幫忙進行分析並給出建議。

Windows 資料收集工具介紹 - TroubleShootingScript toolset (TSS)
https://caryhsu.blogspot.com/2023/11/windows-troubleshootingscript-toolset.html

這三套工具都是由 Microsoft 所推出與維護的，而且也是 Microsoft 的工程師在分析時的工具之一，所以可以安心的使用。

一般在效能分析上，我們簡單的分成長時間的收集與短時間的收集，這個取決於問題多久重現一次，如客戶反應每天的凌晨的排程與相關作業都會變慢，這種有特定的時間點，而且很快的會重現的，我們就可以透過短時間的收集，而如果是很久才會重現一次，而且不確認時間的話，就需考慮透過長時間的觀察來找到問題，長時間與短時間最重要的是在收集時對系統的效能影響，一般來說可能會有5-10%的影響(也有可能更高，取決於系統的使用情況)，所以如果系統一直透過長時間的進行，反而會影響到系統，也會造成因為收集而空間耗用的情況，所以需要簡單先區分類型。

在收集的工具上分成 LogScout 與 PSSDiag 二種，二種都是都用來進行 SQL Server 的效能收集，而且收集後的資訊也都可以透過 SQL Nexus 進行分析。

二者在對最後的分析上沒有一定要使用上那一個工具才可以，但在收集的使用上，坦白說我比較推薦選擇 LogScout，一來此工具比較新，二來 LogScout 不需要像 PSSDiag 要先配制好相對的SQL Server 版本，而且可以在執行時才設定收集的項目等，所以在使用上比較彈性，但我在本篇中也會對二個工具都逐一的介紹。

PS: PSSDiag 可以額外的設定要收集多久的資訊，如1個小時或2個小時，算是一個比較方便的部份。

收集方式：
==============

1. LogScout:

最小需求：

Windows 2012 or later (including Windows Server Core)
Powershell version 4.0, 5.0, or 6.0

說明網址：https://github.com/microsoft/SQL_LogScout

此工具已有內建在 SQL Server on Windows VM 上，所以不需要額外的進行下載。

檔案在下載解壓縮後，只需執行該目錄下的 SQL_LogScout 即可，當下可以選擇透過 GUI 的介面或文字模式進行收集的設定，如下圖，如果透過文字的方式進行時，可以透過數字來選擇需要進行的項目，如進行效能收集，我就會選擇 0+1+4+6+11 進行。

圖型介面

文字介面

如果看到下列的文字，即代表目前已在收集中，所以可以請客戶進行問題的重現，此時請不要關閉視窗，待完成後，再輸入 STOP 就可以完成收集。

收集完成後，您就可以看到指定的目錄中會產生一個 output 的目錄，裡面就是收集的相關檔案。

2. PSSDiag:

最小需求：

Diag Manager

Windows 7 or Windows 10 (32 or 64 bit)
.NET Framework 4.5

說明網址：
https://github.com/microsoft/DiagManager

此工具分成二個部份，第一是需要先確認要收集的 SQL Server 版本，與需要收集那些資料，而且如果是叢集的話，收集方式也會不同，當如果配置錯誤，在執行時就會發生錯誤，如下列所示。

此配置的部份，可以找一台符合最小需求的電腦，不需安裝 SQL Server 即可進行，下載壓縮檔並解開後，執行 DiagManager 的執行檔後，即可進行配置，如下圖，待配置好後，即可點選 Save 進行儲存，儲存後即會在指定的目錄下產生一個檔案，此為主要進行的程式。

將配置好的檔案複制到 SQL Server 上，然後進行解壓縮，並執行 .\pssdiag.ps1 即可進行收集，此時也是請不要關閉視窗，待完成後，輸入 CTRL+C 的組合鍵進行停止即可。

上述的二個收集方式，建議要收集最少15分鐘以上，以免最後在透過 SQL Nexus 進行分析時，無法進行。

分析方式：
==============

SQL Nexus :

在使用 SQL Nexus 前，可以找隨意一台進行，只需符合下列的條件即可，在使用時，需要安裝多個不同的組件，還需要找一台 SQL Server 進行中介處理，所以通常我會找一台測試機進行即可，另外在安裝上，可以直接下載官方的 PowerShell 進行自動判斷安裝與檢查，不用逐一下載，省下很多的步驟。

最小需求：