High Call Volume SQL Server Applications on NUMA Systems

Tuning Parameters Used in TPC-C Benchmarks

The TPC-C benchmark is not as call volume intensive as some applications, but on current very high-end systems, would require techniques to avoid the problem described above. So it is worth examining the call volumes reached and any special configurations employed. The TPC-C benchmark requires a minimum of 55% calls for the four transactions other than the new order transaction, implying that new orders comprise at most 45% of calls. This implies that there are at least 2.22 calls per transaction. So score of 600,000 tpm-C constitutes 10,000 new order transactions per second for a total of 22.2K calls/sec. The table below shows the published TPC-C results for SQL Server 2000 on NUMA systems. The 64-way HP Superdome generates over 29,000 calls per second. Even the 16-way systems require over 12,000 calls per second.

All the above reports specify VI connections between the application server and the database server. The excerpts below are from the Oct-2003 NEC full disclosure report. The first excerpt are the boot options (Itanium system settings may be in the EFI instead of the boot.ini file), one of which explicitly states to be a directive to distribute interrupts. The second excerpt describes connection affinity bindings.

OsLoadOptions = /MAXPROCSPERCLUSTER=1 /CONFIGFLAG=8224
CONFIGFLAG=8224 : Interrupt logging disable
MAXPROCSPERCLUSTER=1: distribute interrupts to all CPUs equally.

We bind each client/server connection (i.e., 8 QLA2350 VIA NICs) onto the distinct CPU sets exclusively.

To do this, apply the following settings by using the SQLServer "Server Network Utility".
Enabled protocols: "VIA" only

Vendor: Select "QLogic"
Listen Info: Enter the following string
"0:1433[0xf],1:1433[0xf0],2:1433[0xf00],3:1433[0xf000],4:1433[0xf0000],5:1433[0xf00000],6:1433[0x7f000000],7:1433[0x80000000]"

Both the Unisys Itanium 2 and Xeon MP reports used the following settings (with some differences).

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\VIA]
"Vendor"="QLogic"
"RecognizedVendors"="QLogic"
"ListenInfo"="0:1421[0x00000003],1:1422[0x0000000c],2:1423[0x00000030],3:1424[0x000000c0],4:1425[0x00000300],
5:1426[0x00000c00],6:1427[0x00003000],7:1428[0x0000c000]"

There is no documentation from Microsoft detailing the values around the port number. The NEC report is most clear in explaining that this is for binding connections to a specific set of CPUs. Appendix C is a collection of excerpts from Microsoft documentation for SQL Server 2005 on NUMA and I/O affinity. The general strategy seems to be as follows. Each group of clients uses a specific port number. The interrupt for that port number is handled by a specific processor, and the user connection in turn will only use processors in the same node. Note that this feature in SQL Server 2005 applies to both TCP/IP and VI. The TPC-C reports for SQL Server 2000 only show the undocumented settings for the VI protocol, but not explicitly state whether there is or is not support for TCP/IP.

It is also unclear as whether the ListenInfo settings only sets affinity for only the network connection itself or also the SQL Server thread and SPID handling any connections open from that port. A look the registry setting for published SQL Server 2005 TPC-C results (Appendix D) indicates additional registry settings for CPU affinity by node.

Additional tuning parameters from the TPC-C full disclosures are as follows. The SQL Server Startup parameters commonly used are:

-T825 Enable NUMA support
-T826 don’t use large pages for buffer array
-T827 enable Super Latches
-T888 Use locked pages for buffers
-E increase the number of consecutive extents allocated per file to x

Below is a setting change in permissions.

Local Policies - User Rights Assignments - policy 'Lock pages in memory'
added group 'Administrators'

The following was used in the HP Superdome report of Oct 2003.
Environment variable LOGAFFINITY specifies which processor the log write will be affinitized to.

Summary

The performance implications of high call volume SQL Server applications on large NUMA based server systems are clearly demonstrated. Microsoft has documented features in SQL Server 2005 involving NUMA memory optimizations and client connection affinity tuning for both the TCP/IP and VI protocols. There are indications that NUMA memory optimization and the client affinity tuning for the VI protocol already exist in SQL Server 2000, but there does not appear to be public documentation. It would be important to understand to what degree the high call volume issue on large systems can be alleviated on TCP/IP over Gigabit Ethernet and when it is necessary to use VI. Anyone with good connections to the Microsoft SQL Server team should make direct inquires if there is reason to believe this subject is important to their environment.

Article copyright 2005 by the author.