Chapter 3: High CPU Utilization .................................................. 73
Investigating CPU Pressure ..................................................................................................74
Performance Monitor ........................................................................................................ 74
SQL Trace ............................................................................................................................ 76
Dynamic Management Views ...........................................................................................77
Common Causes of High CPU Usage .................................................................................85
Missing indexes .................................................................................................................. 86
Outdated statistics ............................................................................................................. 88
Non-SARGable predicates .................................................................................................89
Implicit conversions ............................................................................................................93
Parameter sning .............................................................................................................. 95
Ad hoc non-parameterized queries ................................................................................103
Inappropriate parallelism ................................................................................................ 108
TokenAndPermUserStore ................................................................................................ 117
Windows Server and BIOS power saving options .......................................................120
Summary ................................................................................................................................ 122
Additional Resources ........................................................................................................... 122
Chapter 4: Memory Management ............................................... 126
The Self-Tuning Database Engine ..................................................................................... 127
How SQL Server Allocates Memory .................................................................................. 127
32-bit Virtual Address Space limitations.........................................................................131
Using 64-bit SQL Server ................................................................................................. 140
Memory configuration options with 64-bit SQL Server ............................................143
Diagnosing Memory Pressure ............................................................................................149
Memory-related counters ................................................................................................150
Memory-related DMVs .....................................................................................................154
Common Memory-Related Problems ................................................................................155
The SQL Server memory leak myth ............................................................................... 155
Paging problems ................................................................................................................ 155
OS instability due to Lock Pages in Memory
plus unlimited max server memory ...............................................................................156
App Domain is marked for unload due to memory pressure .................................... 157
