Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 229

NOTE While you probably won't ever need to use 1118 to reduce SGAM
contention, the fact that it forces only uniform extents to be allocated can be
used to increase performance under very heavy workloads, so it might be worth
testing if you're tuning that kind of environment.

Monitoring Tempdb I/O Performance
Troubleshooting SQL Server implies a reactive activity; an issue has occurred that now needs to be
i xed. That may be true but one of the differences that separates an average SQL Server professional
from a good one is knowing about a problem before it has an impact on a live system.

You should be aware by now of tempdb's importance to the overall health of an entire instance, so it
shouldn't be a hard sell to realize the benei ts of being proactive and monitoring tempdb to get early
warning of potential problems before they affect a production system. This section covers the
specii cs of monitoring tempdb I/O: What you should be looking at and what thresholds should
prompt you to do something.

The speed at which requests to store and retrieve data are processed against tempdb is important to
the overall performance of any SQL Server instance and can even be critical where tempdb is either
heavily used or part of an important business process.

Whether you have tempdb on local storage or a SAN (storage area network), on a RAID10
volume or RAID1, the simplest way to check I/O system performance is to look at the latency of I/O
requests. You'll i nd a lot more detailed information about storage, including SANs, RAID levels,
and benchmarking performance, in Chapter 4.

There are two methods for measuring disk latency: using Performance Monitor (see Chapter 10) and
using SQL Server DMVs. Which one you should choose depends on how you want to monitor
performance and how accurate you need it to be.

Performance Monitor
The PerfMon counters that you should be interested in are as follows:

➤

Avg. Disk sec/Transfer

➤

Avg. Disk sec/ Read

➤

Avg. Disk sec/Write

You'l l i nd these grouped under Logical Disk, which shows the logical drives and drive letters
presented in Windows as you would see them in Explorer; and Physical Disk, which shows the
drives as Windows sees them internally. Which group you get the counters from won't matter in
most cases — I tend to use the Logical Disk counters because it's easier to work with drive letters.

The counters themselves all provide the average latency in milliseconds for I/O requests. “Avg. Disk
sec/Transfer” is the combined average for both reads and writes to a drive. This counter provides the
simplest measurement for regular long-term monitoring.