How to configure SQL Server 2000 to use non-uniform memory access (NUMA) (921928)

MORE INFORMATION

In a NUMA system, processors are arranged in smaller systems that are called nodes. Each node has its own processors and memory, and each node is connected to the larger system by using a cache-coherent interconnect bus. On a NUMA computer, the operating system tries to improve performance by scheduling threads on processors that are in the same node as the memory that is being used. The operating system tries to satisfy memory-allocation requests within the node. However, the operating system allocates memory from other nodes if it is necessary. The architecture is non-uniform because each processor is close to some parts of memory and further from other parts of memory. The processor quickly accesses memory to which it is close. However, it can take longer for the processor to access memory that is further away.

If you use a version of SQL Server 2000 that is later than build 8.00.925, SQL Server supports NUMA when SQL Server is running on a Windows Server 2003-based computer that has a set of trace flags enabled.

How to configure SQL Server 2000 to use NUMA

To configure SQL Server 2000 to use NUMA, follow these steps:

1. Make sure that the version of SQL Server is later than 8.00.925. To determine the version of SQL Server, run the following statement in Query Analyzer:

   Select @@version

2. Enable the Lock pages in memory permission for the SQL Server startup account. To do this, follow these steps:
   1. Click Start, click Run, type gpedit.msc, and then click OK.
   2. In the Group Policy Object Editor window, expand Computer Configuration, and then expand Windows Settings.
   3. Expand Security Settings, and then expand Local Policies.
   4. Click Users Rights Assignment. The policies appear in the right pane.
   5. In the right pane, double-click the Lock pages in memory policy.
   6. In the Lock pages in memory Properties dialog box, click Add User or Group.
   7. In the Select Users or Groups dialog box, type the name of the SQL Server startup account, and then click OK.
3. Enable trace flags -T888 and -T825 in the SQL Server service startup parameters. To do this, follow these steps:
   1. Open Enterprise Manager.
   2. In the left pane, right-click the server name, and then click Properties.
   3. Click the General tab, and then click Startup Parameters.
   4. In Startup Parameters - InstanceName dialog box, add a new parameter as -T888, and then add another new parameter as -T825
   Notes
   • The -T888 trace flag enables support for locked pages. When you use this trace flag, memory is allocated together with the MEM_PHYSICAL flag by using the Address Windowing Extensions (AWE). When this trace flag and the Lock pages in memory permission are enabled, the following message is logged in the SQL Server error log:
     DateTime server Using locked pages for buffer pool.
   • The -T825 trace flag enables buffer pool support for NUMA. Trace flag -T825 requires trace flag -T888 to be enabled. Additionally, this trace flag requires that the you set the affinity mask option to a non-zero value.
   • You must set the affinity mask option correctly to use all the processors.
     
     SQL Server affinity interprets the value of the affinity mask option as a bitmask that specifies the processors that are eligible for selection. Number the bits from the right to the left. The rightmost bit is for the first processor, and the second bit from the right is for the second processor. For example, assume that you have 16 processors. If you want to select all 16 processors, the correct setting of the affinity mask option is a hexadecimal value of 0xFFFF or the decimal equivalent of 65535. To set the affinity mask option, run statements that resemble the following in Query Analyzer:

     EXEC sp_configure 'show advanced option', '1'
     reconfigure with override
     go
     EXEC sp_configure 'affinity mask', 0xFFFF
     reconfigure with override
     GO

     Note Trace flag -T8002 changes the meaning of the affinity mask option. The interpretation does not apply when you enable trace flag -T8002. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

     818769 FIX: Trace flag -T8002 treats an affinity mask like a process affinity

4. Consider setting the max degree of parallelism option to the number of processors in a NUMA node. When you set the max degree of parallelism option to the number of processors in the node, SQL Server tries to maintain the memory locality to the local node. Setting the max degree of parallelism option to the number of processors in the node does not guarantee that SQL Server will not access far memory from another NUMA node for parallel queries. However, that behavior is less likely to occur.
   
   For example, if each NUMA node has four processors, you can set the max degree of parallelism option to 4 by using the following statements:

   sp_configure 'max degree of parallelism',4 
   reconfigure
   GO

Troubleshooting

You may experience the following problems when you configure SQL Server 2000 to use NUMA:

• If trace flag -T888 is not enabled, the following error message is logged in the SQL Server error log:
  DateTime server NUMA support requires using locked pages
• If the affinity mask option is not set correctly, the following error message is logged in the SQL Server error log:
  DateTime server NUMA support requires using affinity
• The following log entry indicates that SQL Server 2000 has been successfully configured for locked pages. If the following log entry does not appear, the SQL Server startup account likely does not have the Lock pages in memory permission:
  DateTime server Using locked pages for buffer pool