System Troubleshooting - Pro SQL Server Internals - page 527

SQL Server has three types of memory objects that use HEAP memory. Some of them are created globally on
the server scope. Others are partitioned on a per-NUMA Node or per-CPU basis. You can use startup trace flag
T8048 to switch per-NUMA Node to per-CPU partitioning, which can help reduce CXMEMTHREAD waits at cost of extra
memory usage.

■ You can read more about non-uniform memory access (numa) architecture at: http://technet.microsoft.
com/en-us/library/ms178144.aspx .

Note

Listing 27-8 shows you how to analyze memory allocations of memory objects. You may consider applying the
T8048 trace flag if top memory consumers are per-NUMA Node partitioned, and you can see a large percentage of
CXMEMTHREAD waits in the system. This is especially important in the case of servers with more than eight CPUs per-
NUMA Node, where SQL Server 2008 and above have known issues of per-NUMA Node memory object scalability.

Listing 27-8. Analyzing memory object partitioning and memory usage
select
type
,pages_in_bytes
, case
when (creation_options & 0x20 = 0x20)
then 'Global PMO. Cannot be partitioned by CPU/NUMA Node. T8048 not
applicable.'
when (creation_options & 0x40 = 0x40)
then 'Partitioned by CPU. T8048 not applicable.'
when (creation_options & 0x80 = 0x80)
then 'Partitioned by Node. Use T8048 to further partition by CPU.'
else
'Unknown'
end as [Partitioning Type]
from
sys.dm_os_memory_objects
order by
pages_in_bytes desc

■ You can read an article published by the microsoft CSS team which explains how to debug CXMEMTHREAD wait types
at: http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx .

Note

High CPU Load
As strange as it sounds, low CPU load on a server is not necessarily a good sign. It indicates that the server is
under-utilized. Even though under-utilization leaves systems with room to grow, it increases the IT infrastructure
and operational costs—there are more servers to host and maintain. Obviously, high CPU load is not good either.
Constant CPU pressure on SQL Server makes systems unresponsive and slow.
There are several indicators that can help you detect that a server is working under CPU pressure. These include
a high percentage of SOS_SCHEDULER_YIELD waits, which occur when a worker is waiting in a runnable state. You can
analyze the % Processor Time and Processor Queue Length performance counters and compare the signal and resource