SQL Memory Test

This test monitors the memory usage of a Microsoft SQL server.

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

Total server memory:

This value indicates the total amount of memory that is being currently used by the server.

MB

An unusually large usage of memory by the server is a cause of concern. Further analysis is required to determine if specific users or queries are consuming excess memory.

Target server memory:

This value indicates the total amount of dynamic memory, which the server can consume.

MB

If, over time, the Total server memory measure is less than the Target server memory counter, then this means that SQL server has enough memory to run efficiently. On the other hand, if the Total server memory measure is greater than the Target server memory counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.  

Sql cache memory:

This value indicates the total amount of dynamic memory that the server is using for the dynamic SQL cache.

MB

The amount of data cache available to SQL Server can significantly affect SQL Server’s performance. If the dynamic SQL cache memory usage is low, consider tuning the cache management parameters of SQL server.

Optimizer memory:

This value indicates the total amount of dynamic memory, which the server is using for query optimization.

MB

If the optimizer memory usage is low, consider tuning the optimizer memory management parameters of the Microsoft SQL server.

Max workspace memory:

This value indicates the maximum amount of memory allocated for the execution of processes. This memory is used primarily for operations like hash, sort and create index.

MB

This parameter is useful in conjunction with the grant workspace memory. When the grant workspace memory reaches the max workspace memory then we should consider tuning this.

Lock memory:

This value indicates the total amount of dynamic memory, which the server has allocated for locks.

MB

If the memory allocated for locks is less and there is a contention/wait for a lock, try tuning the lock memory management parameters of the Microsoft SQL server

Grant workspace memory:

This value indicates the total amount of memory granted for the execution of processes. This memory is used for hash, sort and create index operations.

MB

If the grant workspace memory is nearing the maximum workspace memory then the maximum workspace memory may have to be increased.

Connection memory:

This value indicates the total amount of dynamic memory, which the server is using for maintaining connections.

MB

If the memory allocated for connection is less, try tuning the memory management parameters of the Microsoft SQL server.

Memory grants pending:

Indicates the total number of processes waiting for a workspace memory grant.

Number

 In general, if you have any processes queuing waiting for memory, you should expect degraded performance. The ideal situation for a healthy server is no outstanding memory grants - i.e., the value of this measure should ideally be 0.