How To, Folight PI for SQL Server的Performance Counter應用說明

How To, Folight PI for SQL Server的Performance Counter應用說明

我們可以利用Foglight PI for SQL Server來查看SQL Server的Performance Counter，這樣可以更方便來檢視目前SQL Server的效能情形。

在Foglight PI for SQL Server取Windows Server Performance Counter的資料來自WMI Class，與我們在Windows Server 效能監視器取資料的方式不一樣，因為是直接取Raw(原始數值)，而效能監視器是利用Get-Counter -counter 來取Performance Counter的數值，這是格式化後的數值。

WMI Classes related to SQL-Server Instance

• Win32_PerfRawData_{SQL-Server-Instance}AccessMethods
• Win32_PerfRawData_{ SQL-Server-Instance }BufferManager
• Win32_PerfRawData_{ SQL-Server-Instance }CacheManager
• Win32_PerfRawData_{ SQL-Server-Instance }Databases
• Win32_PerfRawData_{ SQL-Server-Instance }DatabaseMirroring
• Win32_PerfRawData_{ SQL-Server-Instance }Latches
• Win32_PerfRawData_{ SQL-Server-Instance }Locks
• Win32_PerfRawData_{ SQL-Server-Instance }PlanCache
• Win32_PerfRawData_ReportServer{ SQL-Server-Instance }_ReportServer{0}Service
• Win32_PerfRawData_{ SQL-Server-Instance }SQLStatistics

WMI Classes related to the OS

• Win32_PerfRawData_PerfDisk_LogicalDisk
• Win32_PerfRawData_PerfDisk_PhysicalDisk
• Win32_PerfRawData_PerfOS_Memory
• Win32_PerfRawData_PerfOS_Processor
• Win32_PerfRawData_PerfProc_Process
• Win32_PerfRawData_PerfOS_System
• Win32_PerfRawData_Tcpip_NetworkInterface
• Win32_ComputerSystem
• Win32_Volume
• Win32_PerfFormattedData_MSDTC_DistributedTransactionCoordinator {0}
• Win32_ComputerSystemProduct
• Win32_Service
• Win32_OperatingSystem

WMI Classes for OS Cluster

• MSCluster_ResourceToDependentResource
• MSCluster_ClusterToQuorumResource
• MSCluster_ClusterToResource
• MSCluster_ResourceGroup
• MSCluster_ResourceGroupToPreferredNode
• MSCluster_ResourceGroupToResourc
• MSCluster_Node
• MSCluster_NodeToActiveGroup
• MSCluster_Resource
• MSCluster_ResourceTypeToResource

接下來，我們來了解如何在Foglight進行SQL Server的Performance Counter檢視資訊的設定步驟。
1.Open Administration | Agents | Agent Status
2.Select a specific agent\instance that you want to add the particular performance counter and click edit | Edit Properties

3.Click "Modify the private properties for this agent."

4. Go to "Performance Counter" and click Edit button

5.Click on "Add Row" button

6.In the "counter name" column put one of the following and in the "units" column choose "count".

7.You need to click Add button for a new counter.
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskWriteQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskReadQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskWriteQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskReadQueueLength
(將原本的Win32_PerfRawData_ 字串改成Win32_PerfFormattedData_)

8.Click "Save Changes" to apply manual setting for Performance Counters.

9.Click Agent Status to go back to Agent Status list。

10.Deactivate and Activate the SQL Server agent

11.Check the manual added Performance Counters will update to Agent Administration Performance Counters list.

12.The User-defined Performance Counters Dashboard display the manual added Performance Counter.
The formatted Performance Counter value is pretty small so we see the statistic graph does not display any statistical graph.
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskWriteQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskReadQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskWriteQueueLength
Win32_PerfFormattedData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskReadQueueLength

執行完上面步驟後，您就可以查詢到您想要查看的Performance Counter資訊了。

但是，為何在Foglight PI for SQL Server的Performance Counter看到的數值這麼大?

I. 例如:設定查看下列Performance Counter項目。
1) Win32_PerfRawData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskWriteQueueLength Count
2) Win32_PerfRawData_PerfDisk_PhysicalDisk(1 E:)\AvgDiskReadQueueLength Count
3) Win32_PerfRawData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskWriteQueueLength Count
4) Win32_PerfRawData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskReadQueueLength Count

II.為何在Windows Server本身的效能監視器看到的數值卻很小?

• Win32_PerfRawData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskWriteQueueLength Count
• Win32_PerfRawData_PerfDisk_PhysicalDisk(0 C:)\AvgDiskReadQueueLength Count

III.在PowerShell執行下列指令查詢Performance Counter的實際數值:
Get-Counter -counter "\PhysicalDisk(0 C:)\Avg. Disk Read Queue Length" -continuous

IV.在PowerShell執行下列指令查詢Performance Counter的實際數值:
Get-Counter -counter "\PhysicalDisk(0 C:)\Avg. Disk Write Queue Length" -continuous

經過上面的步驟的練習，可以了解到如何在Foglight介面查看SQL Server的Performance Counter效能資訊，讓您更快掌握目前SQL Server本機的效能資訊，更快找到資料庫的效能問題。