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

➤

Compilation issues — If possible, identify one user query that is slow, the most common
causes are insufi cient resources. This could be caused by a sub-optimal query plan as a
result of missing or outdated statistics, or inefi cient indexes. Analyze the plan cache to help
identify this problem.

Performance Bottlenecks
Performance troubleshooting involves identifying the bottleneck. This may be done live on the
system, or via a post-mortem review by analyzing data collected during problem occurrence. This is
often an iterative process, each cycle identifying and resolving the largest bottleneck until the prob-
lem is resolved. Often, i xing one bottleneck uncovers another and you need to start the trouble-
shooting cycle again with the new bottleneck.

Memory
If you identify a SQL Server memory bottleneck, you have several options to improve performance.
The i rst is to increase physical memory or change the memory coni guration. Another approach is
to review queries and optimize performance to consume less memory.

If you decide to increase the memory available to SQL Server, you could consider adding more
physical memory, or increasing the memory assignment for virtual machines (VMs). Improving the
use of existing memory without adding more is often more scalable and yields better results. While
x86 (32-bit) systems are becoming less common, if you are running SQL Server 2005 or 2008 on
32-bit systems or VMs, consider using the Address Window Extension (AWE) or /3GB to increase
the buffer pool available to SQL Server (the AWE feature was discontinued in SQL Server 2012).
However, if you do see memory contention on a x86 server, consider a plan to migrate to an × 64
system to resolve this issue. The × 64 platform provides increased virtual memory and better
memory management.

Aside from physical memory and server coni guration, signii cant performance gains can be made
through query tuning to reduce memory requirements. Identify queries that require signii cant mem-
ory grants, such as sorts or hashes, and review the query plans for these scenarios. Try to identify
better indexes, and avoid table scans and other operations that force a large number of rows to be
read from disk and manipulated in memory.

CPU
CPU problems could be sustained or occasional spikes. Occasional CPU spikes, especially for a
small number of CPUs, can often be safely ignored. Wait statistics record the resource SQL Server or
a query is waiting on. Capturing wait statistics information can prove a useful tool in understanding
resource bottlenecks and to identify whether CPU contention is the cause of performance problems.
Consider server build and coni guration options to improve CPU performance, such as increasing
the number and speed of CPU cores. In terms of coni guration options, review the maximum degree
of parallelism to ensure it is optimal for the intended workload.