Hands-on troubleshooting methods on the most recent release of SQL Server
The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server.
- Covers the core technical topics required to understand how SQL Server and Windows should be working
- Shares best practices so that you know how to proactively monitor and avoid problems
- Shows how to use tools to quickly gather, analyze, and effectively respond to the source of a system-wide performance issue
Professional SQL Server 2012 Internals and Troubleshooting helps you to quickly become familiar with the changes of this new release so that you can best handle database performance and troubleshooting.
Autorentext
Christian Bolton is the Technical Director for Coeo Ltd. and a Microsoft Certified Architect, Master, and MVP for SQL Server.
Justin Langford is a Director at Coeo Ltd., a SQL Server MVP, and an author.
Glenn Berry is a Principal Consultant at SQLSkills.com, a SQL Server MVP, and an author.
Gavin Payne is a Senior Consultant for Coeo Ltd. and a Microsoft Certified Master for SQL Server.
Amit Banerjee is a Premier Field Engineer at Microsoft and the owner of TroubleshootingSQL.com.
Rob Farley is Owner/Principal of LobsterPot Solutions, a PASS Director, and a SQL Server MVP.
Wrox Professional guides are written by working developers to address everyday needs. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
Klappentext
Hands-on troubleshooting methods on the most recent release of
SQL Server
The 2012 release of SQL Server is the most significant one since
2005 and introduces an abundance of new features. This critical
book provides in-depth coverage of best practices for
troubleshooting performance problems based on a solid understanding
of both SQL Server and Windows internals and shows experienced DBAs
how to ensure reliable performance. The team of authors shows you
how to master the use of specific troubleshooting tools and how to
interpret their output so you can quickly identify and resolve any
performance issue on any server running SQL Server.
* Covers the core technical topics required to understand how SQL
Server and Windows should be working
* Shares best practices so that you know how to proactively
monitor and avoid problems
* Shows how to use tools to quickly gather, analyze, and
effectively respond to the source of a system-wide performance
issue
Professional SQL Server 2012 Internals and
Troubleshooting helps you to quickly become familiar with the
changes of this new release so that you can best handle database
performance and troubleshooting.
Inhalt
Introduction xxix
Part I: Internals
Chapter 1: SQL Server Architecture 3
Introduction 3
Database Transactions 4
ACID Properties 4
SQL Server Transactions 5
The Life Cycle of a Query 5
The Relational and Storage Engines 6
The Buffer Pool 6
A Basic SELECT Query 7
A Simple Update Query 15
Recovery 18
SQL Server's Execution Model and the SQLOS 22
Execution Model 22
The SQLOS 25
Summary 26
Chapter 2: Demystifying Hardware 29
The Importance of Hardware 29
How Workload Affects Hardware and Storage Considerations 30
Workload Types 30
Server Model Selection 32
Server Model Evolution 33
Processor Vendor Selection 35
Intel Processors 35
AMD Processors and Numbering 43
Choosing and Configuring Hardware for Redundancy 46
Hardware Comparison Tools 48
TPC-E Benchmark 48
Geekbench Benchmark 50
Summary 51
Chapter 3: Understanding Memory 53
Introduction 53
Physical and Virtual Memory 54
Physical Memory 54
Maximum Supported Physical Memory 55
Virtual Memory 56
NUMA 59
SQL Server Memory 63
Memory Nodes 64
Clerks, Caches, and the Buffer Pool 64
Optimizing SQL Server Memory Configuration 70
Min and Max Server Memory 70
Lock Pages in Memory 72
Optimize for Ad-Hoc Workloads 74
Summary 76
Chapter 4: Storage Systems 77
Introduction 77
SQL Server I/O 78
Storage Technology 78
SQL Server and the Windows I/O Subsystem 82
Choosing the Right Storage Networks 84
Shared Storage Arrays 86
Capacity Optimization 86
Storage Tiering 88
Data Replication 89
Remote Data Replication 92
Windows Failover Clustering 93
SQL Server AlwaysOn Availability Groups 94
Risk Mitigation Planning 94
Measuring Performance 95
Storage Performance Counters 96
Disk Drive Performance 97
Sequential Disk Access 100
Server Queues 101
File Layout 101
Partition Alignment 103
NTFS Allocation Unit Size 104
Flash Storage 104
Storage Performance Testing 106
Summary 110
Chapter 5: Query Processing and Execution 111
Introduction 111
Query Processing 112
Parsing 112
Algebrizing 112
Query Optimization 113
Parallel Plans 114
Algebrizer Trees 115
sql_handle or plan_handle 115
Understanding Statistics 116
Plan Caching and Recompilation 117
Influencing Optimization 123
Query Plans 129
Query Plan Operators 132
Reading Query Plans 135
Executing Your Queries 140
SQLOS 140
Summary 147
Chapter 6: Locking and Concurrency 149
Overview 149
Transactions 150
A is for Atomic 150
C is for Consistent 151
I is for Isolated 151
D is for Durable 151
Database Transactions 151
Atomicity 151
Consistency 152
Isolation 152
Durability 152
The Dangers of Concurrency 153
Lost Updates 153
Dirty Reads 155
Non-Repeatable Reads 156
Phantom Reads 158
Double Reads 161
Halloween Effect 162
Locks 163
Monitoring Locks 163
Lock Resources 165
Lock Modes 167
Compatibility Matrix 173
...