SQL Latches Test

A latch is a “lightweight lock”. A latch acts like a lock, in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.

Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, latch wait time must be minimized.

The SQL Latches test makes the following measures of latch activity for a Microsoft SQL database.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter.
  5. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  6. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No. 
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Latch wait rate:

This is the number of latch requests per second that could not be granted immediately.

Waits/Sec

The latch activity may vary from one server to another. Hence, it is important to get baseline numbers for this metric, so that you can compare “typical” latch activity against what is happening currently. If latch activity is higher than expected, this often indicates one of two problems. First, it may mean that the SQL Server could use more memory. If latch activity is high, check the buffer cache hit ratio is. If it is below 99%, the SQL server could probably benefit from more memory. If the hit ratio is above 99%, then it could be the I/O system that is contributing to the problem, and a faster I/O system might improve server’s performance.

Total latch wait time:

This metric denotes the total wait time for latch requests that had to wait in the last second.

Secs

Ideally, this value should be close to 0. The larger this value is, the more contention there is for latches and worse the performance of the database. If the wait time is high, check the SYSPROCESSES table of the database to see which latches are seeing most contention.