SQL Buffer Nodes Test

This test is specific to Microsoft SQL Server 2005 (or above).

Microsoft SQL Server 2005 (or above) is non-uniform memory access (NUMA) aware. As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. NUMA architecture provides a scalable solution to this problem. SQL Server 2005 (or above) has been designed to take advantage of NUMA-based computers without requiring any application changes. This database server groups schedulers to map to the grouping of CPUs, based on the hardware NUMA boundary exposed by Windows. For example, a 16-way box may have 4 NUMA nodes, each node having 4 CPUs. This allows for a greater memory locality for that group of schedulers when tasks are processed on the node. With SQL Server 2005 (or above) you can further subdivide CPUs associated with a hardware NUMA node into multiple CPU nodes. This is known as soft-NUMA.  There is one SQL Server memory node for each physical NUMA node. When a thread running on a specific hardware NUMA node allocates memory, the memory manager of SQL Server tries to allocate memory from the memory associated with the NUMA node for locality of reference. Similarly, buffer pool pages are distributed across hardware NUMA nodes. It is more efficient for a thread to access memory from a buffer page that is allocated on the local memory than to access it from foreign memory.

To understand the local vs. foreign memory distribution in SQL Server better, assume that the computer has 16 gigabytes (GB) of memory. Other applications including Windows have consumed some of the memory from each node, SQL Server has assigned some memory for its processes outside of the buffer pool, and SQL Server has 10 GB of memory to assign to the buffer pool. The buffer pool memory is divided among four physical NUMA nodes, N0, N1, N2, and N3, each with the following local memory available:

  • N0 – 1 GB
  • N1 – 3 GB
  • N2 – 3 GB
  • N3 – 3 GB

In the above configuration, all nodes will eventually allocate and use 2.5 GB of memory; however, node N0 will end up with 1.0 GB of its own memory, and 1.5 GB of memory from other nodes.

The SQL Buffer Nodes test reports information about buffer pool page distribution for each NUMA node on MSSQL Server 2005 (or above).

Target of the test : A Microsoft SQL server 2005 (or above)

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every NUMA node on the Microsoft SQL Server 2005 (or above)

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. ssl - By default, the SSL flag is set to No, indicating that the target Microsoft SQL server is not SSL-enabled by default. To enable the test to connect to an SSL-enabled Microsoft SQL server, set the SSL flag to Yes.
  6. 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.
  7. 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

Database pages:

Indicates the number of pages that are currently in the buffer pool with database content.

Number

 

Foreign pages:

Indicates the number of pages that were currently received from other NUMA nodes.

Number

If the SQL server is running on non-NUMA hardware, then the value of this measure will be 0.

Foreign pages will not be used during ramp-up because they can frequently be transferred to the owning node and become local to that node. When the value of max server memory is reached, some nodes may have foreign memory, but once the memory target is achieved, the buffer pool will treat local and foreign memory identically. For example, under memory pressure, the buffer pool will not make any effort to free up foreign memory pages before local memory pages.

If the value of this measure increases consistently, it indicates that local memory on the node is inadequate.

Free pages:

Indicates the total number of pages that are currently free on this NUMA node.

Number

If the value of this measure dips consistently, it is indicative of insufficient memory on the node. You might want to consider resizing the buffer pool in this case.

Page life expectancy:

Indicates the number of seconds a page currently stayed in the buffer pool without references.

Seconds

 

Stolen pages:

Indicates the number of pages that were currently used for miscellaneous server purposes (stolen from the buffer pool) on this node.

Number

 

Target pages:

Indicates the ideal number of pages in the buffer pool on this node.

Number

 

Total pages:

Indicates the total number of committed pages that currently exist in the buffer pool on this node.

Number