LoadRunner監控SQLServer

TIB發表於2010-02-03
SQLServer

新增SQL Server監視出錯

Monitor name :MS SQL Server. Cannot create measurement SQLServer|User Connections| on machine 127.0.0.1.

Details: 在計算機上找不到指定的物件。

 

Hint:

Check that there is such a measurement on this machine.

Check that you selected the right platform for the monitored machine in the Add Machine dialog box.

Note: When you monitor a Win2000 machine from an NT, some counters may not be available.

Try to delete this measurement and add it again (use the Add Resources dialog box ) (entry point: CFactory::Addmeasurement ).       [MsgId: MMSG-47296]

 

監控SQLSERVER時，能增加度量。但是隻有系統資源相關的度量有資料，而和sqlserver相關的度量卻沒有資料。

 

解決方法：

改為在System Resource Graphs中通過新增Windows Resources的方式新增SQLServer計數器則可以，新增的物件是SQLServer:General Statistics，計數器選User Connections

 

應該收集哪些SQLServer的效能指標？

一般需要監控以下指標：

1） SQLServer資源監控中指標快取點選率（Cache Hit Ratio），該值越高越好。如果持續低於80%，應考慮增加記憶體。

2） 如果Full Scans/sec（全表掃描/秒）計數器顯示的值比1或2高，則應分析你的查詢以確定是否確實需要全表掃描，以及SQL查詢是否可以被優化。

3） Number of Deadlocks/sec(死鎖的數量/秒)：死鎖對應用程式的可伸縮性非常有害，並且會導致惡劣的使用者體驗。該計數器的值必須為0。

4） Lock Requests/sec(鎖請求/秒)，通過優化查詢來減少讀取次數，可以減少該計數器的值。

 

具體可參考：

1、Controller幫助文件：

The following table describes the default counters that can be monitored on version 6.5 of the SQL Server:

Measurement

Description

% Total Processor Time (NT)

The average percentage of time that all the processors on the system are busy executing non-idle threads. On a multi-processor system, if all processors are always busy, this is 100%, if all processors are 50% busy this is 50% and if 1/4 of the processors are 100% busy this is 25%. It can be viewed as the fraction of the time spent doing useful work. Each processor is assigned an Idle thread in the Idle process which consumes those unproductive processor cycles not used by any other threads.

% Processor Time (Win 2000)

The percentage of time that the processor is executing a non-idle thread. This counter was designed as a primary indicator of processor activity. It is calculated by measuring the time that the processor spends executing the thread of the idle process in each sample interval, and subtracting that value from 100%. (Each processor has an idle thread which consumes cycles when no other threads are ready to run). It can be viewed as the percentage of the sample interval spent doing useful work. This counter displays the average percentage of busy time observed during the sample interval. It is calculated by monitoring the time the service was inactive, and then subtracting that value from 100%.

Cache Hit Ratio

The percentage of time that a requested data page was found in the data cache (instead of being read from disk).

I/O - Batch Writes/sec

The number of 2K pages written to disk per second, using Batch I/O. The checkpoint thread is the primary user of Batch I/O.

I/O - Lazy Writes/sec

The number of 2K pages flushed to disk per second by the Lazy Writer.

I/O - Outstanding Reads

The number of physical reads pending.

I/O - Outstanding Writes

The number of physical writes pending.

I/O - Page Reads/sec

The number of physical page reads per second.

I/O - Transactions/sec

The number of Transact-SQL command batches executed per second.

User Connections

The number of open user connections.

 

2、《Performance Monitoring Best Practices》中的MS SQL Server Monitoring部分。

安裝LR9.51補丁後可以在C:/Program Files/HP/LoadRunner/help目錄中找到Monitoring_BP.pdf檔案。

 

3、參考：

http://www.loadtester.com/files/sql2000_perfmon.pdf

 

相關文章