Microsoft SQL Server
Supported versions
SQL Server monitors allow monitoring all versions of Microsoft SQL Server.
Connection settings
The available performance objects are:
- SQL Server
- All objects starting with "MSSQL$SQLEXPRESS:"
As the monitor uses the same technology as the Perfmon monitoring tool included with Windows, NeoLoad is able to access all the counters available for SQL Server.
Microsoft monitors use a Windows API to list and query performance counters, namely the Performance Data Helper (PDH) library used by Perfmon which is a Windows tool. Therefore, the NeoLoad Microsoft monitors have the same characteristics and constraints as Perfmon:
- Monitoring is done without installing any additional component, which obviates the installation of a specific client on the machine to be monitored.
- The monitoring of Microsoft servers is only possible using Windows monitoring agents. Microsoft servers cannot be monitored with a Unix/Linux monitoring agent.
- The account used to start the monitoring agent (or NeoLoad for the built-in agent) must have the appropriate user rights. See Connect to a remote server.
When defining a counter, and depending on the type of counter, one or more instances may be also selected. One instance corresponds to a certain sub-division of the monitored object. For example, Processor/Processor Time % has several instances, one for each processor present in the monitored machine. Usually, an instance named _Total allows retrieving an average value across all available instances.
Create an SQL Server monitor
NeoLoad makes it possible to create a new monitor either using the monitored machine creation wizard, as described in Create and configure a monitored machine, or from an existing monitored machine, as described in Create and configure a monitor.
Available counters
On a typical Windows server, the main, basic counters are as follows:
- SQL Server\Buffer Management: Buffer cache hit ratio. Indicates how often SQL Server goes to the buffer, not the hard disk, to get data. Should be greater than 85.
- SQL Server\Memory Manager: Total Server Memory (KB). If this reaches the level of the overall server memory, it means the server needs more RAM.
- SQL Server\general statistics: User connections. Number of users connected to the system.
- HardConnectsPerSecond. Number of connections per second that are being made to a database server.
- HardDisconnectsPerSecond. Number of disconnects per second that are being made to a database server.
- NumberOfActiveConnectionPoolGroups. Number of unique connection pool groups that are active. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
- NumberOfActiveConnectionPools. Total number of connection pools. When connection pooling is configured in the applications, connection pools will be created and connections (min Size mentioned) will be established.
- NumberOfActiveConnections. Number of active connections that are currently in use.
- NumberOfFreeConnections. Number of connections available for use in the connection pools.
- NumberOfInactiveConnectionPoolGroups. Number of unique connection pool groups that are marked for pruning. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
- NumberOfInactiveConnectionPools. Number of inactive connection pools that have not had any recent activity and are waiting to be disposed.
- NumberOfNonPooledConnections. Number of active connections that are not pooled.
- NumberOfPooledConnections. Number of active connections that are being managed by the connection pooling infrastructure.
- NumberOfReclaimedConnections. Number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application. Not explicitly closing or disposing connections hurts performance.
- NumberOfStasisConnections. Number of connections currently awaiting completion of an action and which are therefore unavailable for use by your application.
- SoftConnectsPerSecond. Number of active connections being pulled from the connection pool.
- SoftDisconnectsPerSecond. Number of active connections that are being returned to the connection pool.