Applies to ReadyAPI 2.7, last modified on June 25, 2019

You use MSSQL monitors to check metrics of your MSSQL databases.

Note: You can get additional metrics from MS SQL Server 2017 by using the Microsoft SQL Server 2017 monitor.

Requirements

To monitor an MSSQL database, you need to prepare it in the same way you prepare Windows servers for testing:

  • ReadyAPI must run on a Windows machine. Windows, IIS and MSSQL monitors are not available on Linux and macOS machines.

  • The Remote Registry service must be enabled on the server.

  • Ports 139 and 445 must be open on the server, the computer with LoadUI and any proxy between them.

  • The user account must have Performance Monitor or Administrator permissions on the server.

For more information on how to prepare Windows machines for testing, see Preparing Servers For Monitoring.

Monitor Settings

  • Instance name – The name of the server instance. If you want to use the default instance, leave the field empty.

Frequently Used Metrics

MSSQL database metrics contain information about database usage during a test run. LoadUI displays the following statistics in charts by default:

  • SQLServer:Resource Pool Stats – CPU usage % – internal – The amount of the CPU used by the SQL server resource pool. This is a CPU percentage used on the agent machine. If your server is bottlenecked by the CPU, check this value.

  • SQLServer:SQL Statistics – SQL Compilations/sec – The number of SQL compilations per second. Compiling takes extra time and makes responses slower, so this value should be low.

  • SQLServer:SQL Statistics – Batch Requests/sec – The number of Transact-SQL command batches received per second. A high value in these statistics means a good throughput.

Here are some other frequently used metrics:

  • SQLServer:SQL Errors – Errors/sec – Total – The number of SQL errors happening per second. The lower this value is, the better. You can monitor individual metrics for particular error types if you need to determine what errors happen in your database.

  • SQLServer:Wait Statistics – WaitType – Average wait time (ms) – The average time it takes to handle database requests. These requests may take long, so this value should be as low as possible.

  • SQLServer:Wait Statistics – WaitType – Waits in progress – The number of waits for various wait types. This value may go up if you simulate a sudden spike of a load, but should generally be pretty low.

  • SQLServer:Memory Manager – Free Memory (KB) – Specifies the amount of memory provided to the database, but not used. If there is a lot of free memory, your database will handle an increased load, but this requires more hardware resources. If you run out of free memory, server responses will slow down significantly.

  • SQLServer:Memory Manager – Granted Workspace Memory (KB) – The amount of memory currently used to work with the database. You can add this value to the Free Memory (KB) metric to see how much memory the database requires. If you run into problems with memory on the machine, check these two metrics.

Other Metrics

LoadUI can access a number of SQLServer objects. These objects provide access to all resources of the SQL Server. For a full list of available statistics, see the Use SQL Server Objects article in MSDN.

See Also

MSSQL Server 2017 Monitor
Preparing Servers for Monitoring
Server Inspector
Load Testing Scenarios

Highlight search results