2012-12-07

[SQL][Performance]利用效能監視器了解 SQL Server 運作狀況

34500
0
SQL Performance Tuning
2012-12-08

[SQL][Performance]利用效能監視器了解 SQL Server 運作狀況

對 SQL Server 的 DBA 來說，利用效能監視器了解 SQL Server 運作狀況應該不是一件新奇的事情，甚至很多的研討會和資料上來說都有說明要查看那些資訊，對大家來說應該都不陌生才對。而最近剛好遇到一些效能調教的案件，但因為不是很方便連線進行處理，因此把相關處理步驟整理一下，讓一些在前線的同事們可以很快地來進行第一手的判斷，加速問題的處理。因此我將效能監視器上的相關設定方式和查看方式給整理一下，這樣以後有需要的朋友就請他按圖施工囉。

設定

一般來說效能監視器有兩種設定的方式，一種是透過啟動效能監視器透過 GUI 的方式進行設定，而另一種則是透過命令列，利用 Logman 進行設定。兩種能達到的方式都相同，利用 GUI 雖然看起來動作比較多，但可以利用建立範本 ( Template ) 的方式將設定儲存起來，這樣下次要設定的時候就不用一個一個挑選，用匯入的方式就可以了；而透過 Logman 也是很不錯的方法，只要將相關指令存成批次檔，那下次需要到不同環境或是不同監控數據的時候，則只要修改批次檔後就可以快速使用了。首先我們先確定要收集的項目，這裡我參考一些之前研討會和相關的網站資料，列出一些比較常用的收集項目 :

因此可以在啟動效能監視器之後，選擇「資料收集器集合工具」的「使用者定義」，在上面按下滑鼠右鍵選擇「新增」→「資料收集集合工具」，看是要匯入之前已經定義好的範本還是手動建立

如果不嫌麻煩可以按造前面的表格將所需要的計數器一個一個給加入，或者是將以下的設定存成 XML 檔案，當成範本給匯入也可以

<?xml version="1.0" encoding="UTF-16"?>
<DataCollectorSet>
    <Status>0</Status>
    <Duration>0</Duration>
    <Description>
    </Description>
    <DescriptionUnresolved>
    </DescriptionUnresolved>
    <DisplayName>
    </DisplayName>
    <DisplayNameUnresolved>
    </DisplayNameUnresolved>
    <SchedulesEnabled>-1</SchedulesEnabled>
    <LatestOutputLocation>
    </LatestOutputLocation>
    <Name>TEST</Name>
    <OutputLocation>C:\PerfLogs\Admin\SQLServer</OutputLocation>
    <RootPath>C:\PerfLogs\Admin</RootPath>
    <Segment>0</Segment>
    <SegmentMaxDuration>0</SegmentMaxDuration>
    <SegmentMaxSize>0</SegmentMaxSize>
    <SerialNumber>1</SerialNumber>
    <Server>
    </Server>
    <Subdirectory>
    </Subdirectory>
    <SubdirectoryFormat>3</SubdirectoryFormat>
    <SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
    <Task>
    </Task>
    <TaskRunAsSelf>0</TaskRunAsSelf>
    <TaskArguments>
    </TaskArguments>
    <TaskUserTextArguments>
    </TaskUserTextArguments>
    <UserAccount>SYSTEM</UserAccount>
    <Security>O:BAG:S-1-5-21-687209553-3516051733-1982557323-513D:AI(A;;FA;;;SY)(A;;FA;;;BA)(A;;FR;;;LU)(A;;0x1301ff;;;S-1-5-80-2661322625-712705077-2999183737-3043590567-590698655)(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200ab;;;LU)(A;ID;FR;;;AU)(A;ID;FR;;;LS)(A;ID;FR;;;NS)</Security>
    <StopOnCompletion>0</StopOnCompletion>
    <PerformanceCounterDataCollector>
        <DataCollectorType>0</DataCollectorType>
        <Name>DataCollector01</Name>
        <FileName>Performance</FileName>
        <FileNameFormat>1</FileNameFormat>
        <FileNameFormatPattern>MMdd</FileNameFormatPattern>
        <LogAppend>0</LogAppend>
        <LogCircular>0</LogCircular>
        <LogOverwrite>0</LogOverwrite>
        <LatestOutputLocation>
        </LatestOutputLocation>
        <DataSourceName>
        </DataSourceName>
        <SampleInterval>30</SampleInterval>
        <SegmentMaxRecords>0</SegmentMaxRecords>
        <LogFileFormat>3</LogFileFormat>
        <Counter>\Memory\Available MBytes</Counter>
        <Counter>\Memory\Page Reads/sec</Counter>
        <Counter>\PhysicalDisk(_Total)\Avg. Disk Read Queue Length</Counter>
        <Counter>\PhysicalDisk(_Total)\Avg. Disk Write Queue Length</Counter>
        <Counter>\Processor Information(_Total)\% Processor Time</Counter>
        <Counter>\SQLServer:Access Methods\Page Splits/sec</Counter>
        <Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
        <Counter>\SQLServer:General Statistics\User Connections</Counter>
        <Counter>\SQLServer:Memory Manager\Target Server Memory (KB)</Counter>
        <Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>
        <Counter>\System\Processor Queue Length</Counter>
        <CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
        <CounterDisplayName>\Memory\Page Reads/sec</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Read Queue Length</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Write Queue Length</CounterDisplayName>
        <CounterDisplayName>\Processor Information(_Total)\% Processor Time</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Access Methods\Page Splits/sec</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
        <CounterDisplayName>\SQLServer:General Statistics\User Connections</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\System\Processor Queue Length</CounterDisplayName>
    </PerformanceCounterDataCollector>
    <DataManager>
        <Enabled>0</Enabled>
        <CheckBeforeRunning>0</CheckBeforeRunning>
        <MinFreeDisk>0</MinFreeDisk>
        <MaxSize>0</MaxSize>
        <MaxFolderCount>0</MaxFolderCount>
        <ResourcePolicy>0</ResourcePolicy>
        <ReportFileName>report.html</ReportFileName>
        <RuleTargetFileName>report.xml</RuleTargetFileName>
        <EventsFileName>
        </EventsFileName>
    </DataManager>
</DataCollectorSet>

在這裡我選擇用範本匯入之前的設定後，就可以很快的完成。設定完後比較要注意的是紀錄格式，可以在「效能計數器」上面按下滑鼠右鍵，選擇「內容」來做調整，預設是採用二進位格式，也可以調整成為逗點分隔將檔案存成 CSV 格式，這樣後續可以用 Excel 匯入製作圖表；亦或者是將這些資料存放到 SQL Server 上面，後續可以配合 SQL 指令或者是 Report Service 來查看資料。

LOGMAN

如果需要利用指令來處理的話，那麼 Logman 跟 GUI 設定方式一樣提供給我們兩種方式，可以直接利用 Logman 去建立相關的計數器，或者是利用匯入 XML 範本的方式來做建立。此時我先啟動一個「以系統管理員身分執行」的命令提示字元環靜，使用指令：

logman import "SQL Performance" -xml SQL_Performance_Templete.xml

就可以將之前所產生的範本給匯入。但如果要用 Logman 建立的話，可以參考範本檔裡面的 Counter 資訊，取出來放在文字檔案裡面，這樣建立的時候比較方便，如果想知道全部有哪些計數器可以使用的話，可以透過 "typeperf –q" 的方式列出來，在這裡我先整理出此次要用的計數器資訊如下：

\Memory\Available MBytes
\Memory\Page Reads/sec
\PhysicalDisk(_Total)\Avg. Disk Read Queue Length
\PhysicalDisk(_Total)\Avg. Disk Write Queue Length
\Processor Information(_Total)\% Processor Time
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:General Statistics\User Connections
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)

我將這些資料存成 SQL_Performance_List.TXT 的檔案，因此接下來就可以利用指令來建立了

logman create counter "Logman Test" -s %computername% -cf SQL_Performance_List.txt

因為範本檔案裏面設定的資訊比較多，因此當使用 create counter 的參數的時候，還可以另外再多加上其他的參數去指定檔案大小，起始時間，檔案格式等資訊。這個部分可以參考 MSDN 網站上的說明。

報表製作與分析

透過效能監視器的設定，我們可以很方便的取得相關檢測資料，但那麼多的資料要怎麼去判斷哪些地方有問題呢 ? 以往筆者自己的做法多半是把資料儲存為 CSV 檔案，然後使用 Excel 來製作圖表，然而在尋找相關資料的時候，正巧看到一套不錯的軟體 - Performance Analysis of Logs (PAL) Tool ( 網址 )。他主要是透過 PowerShell , LogParse 和 Chart Control 元件，幫你將透過效能監視器所收集到的資料轉換成為圖表的樣式，並且加上建議處理，就算您不知道要用效能監視器去收集那些資訊，他也可以產生出效能監視器的 Template 檔案，讓你可以很快速的就設定好，算是非常方便的工具。在網站上有區分 32 位元和 64 位元的版本，看您要在甚麼樣的環境去執行，挑選回來安裝就好。

執行的畫面

如果要進行分析，可以在「Counter Log」這頁中去指定你效能分析的檔案名稱

如果不知道要收集那些資料，可以透過「Threshold File」這頁中去找相關的範本來使用，預計就有 Exange Server , SQL Server , Project Server , Hyper-V 等的樣板可以來使用了，選擇好之後可以透過「Export to Perfmon template file…」的按鈕去指定樣板檔案匯出的名稱，就可以將該檔案透過前面的設定過程或者是 Logman，在要收集資訊的主機上來進行資料收集了。