Viewing Server Performance with PerfMon and the PAL Tool - SQL Server 2012 Internals and Troubleshooting - page 281

Common Causes of CPU Problems
This section describes three common causes of high CPU usage conditions:

➤

Missing Statistics or Outdated Statistics — The Query Optimizer is dependent on relevant
statistics to determine a good execution plan. Therefore, missing or outdated statistics could
cause the Query Optimizer to select a sub-optimal plan, causing excessive CPU consumption.

➤

Missing Indexes — A lack of useful indexes can result in a high-CPU condition. SQL Server
is dependent on meaningful indexes to retrieve data efi ciently, and missing indexes often
cause excessive CPU utilization. A lack of useful indexes can result in expensive operations,
such as hash joins and sorts that could be avoided with improved indexes.

➤

Excessive Recompilation — Poor plan reuse can cause a high-CPU condition whereby SQL
Server consumes excessive CPU cycles while generating query plans. Recompilations can
be caused by ad hoc or dynamic queries or by a lack of memory (procedure cache), causing
plans to be dropped from cache.

Investigating Memory-Related Problems
SQL Server performance is closely related to the availability and performance of sufi cient
memory. SQL Server coni guration-related memory settings include the following:

➤

sp_coni gure
➤

Min/max server memory

➤

AWE Enabled

➤

Min memory per query

➤

Windows
➤

/3GB, /USERVA, /PAE (in 32-bit environments)

➤

Lock Pages in Memory privilege

Typically, using the Windows Task Manager doesn't provide the best measure of the memory con-
sumed by SQL Server. Using PerfMon is a more reliable method of measuring memory consumption,
since this includes all types of memory allocation that can be made by SQL Server. You can also
refer back to Chapter 3 for more details on memory.

Types of Memory Pressure
SQL Server can suffer from internal or external memory pressure, and understanding how to iden-
tify and troubleshoot each will enable more targeted troubleshooting. External memory pressure
occurs most often when SQL Server is running on a shared computer and several processes are
competing for memory. In this situation, Resource Monitor within SQL Server Operating System
(SQLOS) receives a signal from Windows to request that SQL Server reduce its committed memory.
This causes SQL Server to recalculate its target commit level, and reduce it if necessary.
Internal memory pressure occurs when multiple SQL Server resources compete with each other for
memory. This typically causes SQL Server to shrink the data cache, which can impact server perfor-
mance. Use the DBCC MEMORYSTATUS command to gain visibility of SQL Server memory consumption.