System Troubleshooting - Pro SQL Server Internals - page 533

You should check network performance and analyze the client code if you see a large percentage of
ASYNC_NETWORK_IO waits in the system.

Allocation Map Contention and Tempdb load
Allocation map pages contention exposes itself with PAGELATCH_* wait types. These wait types indicate contention on
in-memory pages as opposed to PAGEIOLATCH wait types, which are I/O subsystem related.

■ latches are lightweight synchronization objects that protect the consistency of SQl Server internal data
structures. For example, latches prevent multiple sessions from changing an in-memory data page simultaneously and
corrupting it.

Note

Coverage of latches is beyond the scope of this topic. You can read more about latches and latch contention
troubleshooting at: http://www.microsoft.com/en-us/download/details.aspx?id=26665 .

Allocation map pages contention rarely happens in user databases unless the data is highly volatile. One example
is a system that collects data from external sources with very high inserts and, therefore, pages and extents allocations
rates. However, as we have already discussed, allocation map pages contention could become a major performance
bottleneck in the case of tempdb .
When you see a large percentage of PAGELATCH waits, you should locate the resources where contention occurs.
You can monitor the wait_resource column in the sys.dm_exec_requests or resource_description columns in
the sys.dm_os_waiting_tasks view for corresponding wait types. The information in those columns includes the
database id, file id, and page number. You can reduce allocation map contention in the corresponding database by
moving objects that lead to the contention to another filegroup with a larger number of data files.

■ You can move objects by performing an index rebuild in the new filegroup. make sure that all data files in
the new filegroup were created with the same size and auto-growth parameters, which evenly balances write activity
between them.

Tip

remember that moving lob data requires extra steps, as we have already discussed in Chapter 15, “Data partitioning.”

In the case of allocation map contention in tempdb (database id is 2), you can prevent mixed extents allocation
with T1118 trace flag and create temporary objects in a way that allows their caching. We have already discussed this
in detail in Chapter 12, “Temporary Tables.”
Other tempdb related performance counters, which can help you monitor its load, include Version Store
Generation Rate (KB/S) , Version Store Size (KB) in Transactions Object and Temp Table Creation Rate, and Active Temp
Tables in a General Statistics Object. Those counters are most useful with the baseline, where they can show the trends
of tempdb load.

Wrapping Up
Table 27-1 shows symptoms of the most common problems you will encounter in systems, and it illustrates the steps
you can take to address these problems.