Troubleshooting Methodology and Practices - SQL Server 2012 Internals and Troubleshooting - page 251

In many situations, overall performance may be acceptable while the server demonstrates high CPU.
As with memory, once you have established CPU is the dominant wait type, identify the top 10
worst-performing queries by CPU and then work through each of these in turn. Look at the query
execution plan and identify expensive CPU operations, such as hash joins, sorts, and computed
columns. Look for opportunities to reduce CPU workload with new indexes, consolidated indexes,
XML indexes, or to improve query design.

Storage I/O
Storage input/output (I/O) is typically the slowest resource within a server (memory and CPU are
orders of magnitude quicker). Therefore, optimizing the storage solution design and coni guration
(ensuring the solution performs optimally) as well as being considerate with I/O requests (mak-
ing fewer I/O requests) is essential to achieve scalable systems with good performance. Review the
PerfMon disk counters for Average Disk Sec/Read and Average Disk Sec/Write to verify that the
time to make a read or write is ideally below 20 milliseconds for OLTP systems, higher for deci-
sion support systems. Generally speaking, if storage is performing slower than this, database per-
formance will be affected. When reviewing storage performance, consider the end-to-end solution.
Following are some elements that may affect performance:
➤

RAID levels

➤

Disk types (enterprise l ash Disk, SCSI)

➤

Dedicated or shared disk arrays

➤

Connectivity (Ini niBand, Fibre Channel, iSCSI)

➤

HBA cache and queue settings

➤

HBA load balancing policy (active; active vs. active; or passive)

➤

NTFS cluster size

➤

Layout and isolation of data, index, log, and tempdb i les

➤

Storage cache and controllers policy
In addition to ensuring optimal storage performance, be smart with I/O and ensure that the data-
base is not making unnecessary requests. Reviewing and optimizing a query plan to eliminate index
scans and replace them with seeks can often deliver an order of magnitude benei t in I/O reduction.
It is common to overwhelm the storage solution with inefi cient queries, saturating controllers and
cache on the storage array.

Reduce I/O workload by improving indexes for more efi cient access, make sure statistics are
current, tune or increase memory to improve cache performance, or alter queries to avoid unneces-
sary I/O. Rationalize and consolidate indexes to minimize the overhead of index maintenance. Use
Proi ler or DMVs to identify the worst-performing queries by reads and writes. In addition, use
STATISTICS IO to identify batches within a query that contain high logical I/Os. Usually, identify-
ing the table or view that has the highest number of logical I/Os is sufi cient to identify the table or
view requiring optimization.