Microsoft SQL Server 2000 Performance Tuning Technical Reference

by Microsoft Corporation

• ISBN13: 9780735612709
• ISBN10: 0735612706
• Edition: 1st
• Format: Paperback
• Copyright: 2001-07-03
• Publisher: Microsoft Press

Summary

Performance tuning a relational database can be engaging yet frustrating, and this guide gives you the practical information you need to configure and tune a Microsoft® SQL Server'„¢ 2000 database for better, faster, more scalable solutions. The authors start with the basics and build upon them to teach the mechanics of performance tuning and how they affect the whole system. This book also shows how to optimize for the underlying operating system and hardware. It's the only book of its kind coauthored by engineers who have worked in the SQL Server performance group. Expert instruction helps you understand these topics: THE BASICS: Architectural fundamentals that affect tuning I/O tuning and RAID storage considerations How to tune hardware, database layout, and configuration parameters Feature enhancements for better ease-of-use, performance, manageability, and reliability. SERVER TUNING: How to use the Microsoft Windows® 2000 System Monitor and the SQL Server Profiler to shorten transaction response times. SIZING AND CAPACITY PLANNING: How to model software and hardware usage to predict resource consumption and conduct preconfiguration planning, and how to perform what-if scenarios about workload growth to avoid slow response times. CONFIGURING AND TUNING: How to tune online transaction processing (OLTP) systems, data warehouses, and replicated systems. How to set up your system for high-performance backup and recovery. TUNING SQL STATEMENTS: How to get optimal performance by using Query Analyzer and Profiler to tune SQL statements and stored procedures. How to take advantage of indexes and hints.

Author Biography

Edward Whalen is an expert in database performance, administration, and backup recovery solutions.

Table of Contents

Tables	xiv
Acknowledgments	xv
Introduction	xvii
PART I Basic Concepts
Performance Tuning, Capacity Planning, and Sizing Overview	3	(10)
Performance Tuning and Optimization	3	(2)
Application Tuning	4	(1)
SQL Server Tuning	5	(1)
Hardware Tuning	5	(1)
Sizing and Capacity Planning	5	(1)
Server Tuning Methodology	6	(5)
Tuning Steps	6	(3)
Tuning Tips and Recommendations	9	(2)
Summary	11	(2)
SQL Server 2000 Architecture Fundamentals	13	(26)
Memory Management	13	(7)
Concepts: Physical and Virtual Memory	13	(1)
SQL Server Memory Architecture	14	(3)
Dynamic and Manual Memory Configuration	17	(2)
Memory Sizes Supported	19	(1)
Data Storage	20	(6)
Data Files and Log Files	20	(2)
Filegroups and File Placement	22	(3)
Automatic File Growth	25	(1)
Pages, Extents, and Rows	25	(1)
Lock Management	26	(2)
What is a Lock?	26	(1)
Lock Granularity and Lock Modes	27	(1)
The locks Option	28	(1)
Thread Management	28	(2)
Fiber Mode Scheduling and Worker Threads	29	(1)
Transaction Log	30	(8)
Transaction Log Characteristics	31	(1)
Transaction Log Architecture	32	(2)
Truncating the Transaction Log	34	(1)
What is a Checkpoint?	35	(3)
Summary	38	(1)
Understanding the I/O Subsystem and RAID	39	(26)
Performance Characteristics of Disk Drives	39	(8)
Disk Drive Description	40	(1)
Disk Drive Behaviors	41	(2)
Disk Drive Specifications	43	(1)
Disk Drive Reliability	44	(1)
Disk Drive Performance	44	(3)
Solving the Disk Performance Capacity Problem	47	(1)
Introduction to RAID	47	(3)
Controller Caches	48	(1)
Internal vs. External RAID	48	(1)
Storage Area Networks	49	(1)
Common RAID Levels	50	(8)
Striping	50	(1)
RAID 0	51	(1)
RAID 1	52	(2)
RAID 5	54	(2)
RAID 10	56	(2)
Performance Comparison of RAID Levels	58	(3)
Read Performance	58	(1)
Write Performance	59	(1)
Disk Calculations	59	(1)
Choosing the Right RAID Level	60	(1)
I/O Latencies and SQL Server	61	(1)
Guidelines for Configuring I/O Subsystems	62	(1)
Summary	63	(2)
System Tuning	65	(24)
How to Measure Performance	65	(2)
Tuning the Server with Hardware	67	(11)
Processor Architecture	67	(2)
Tuning the Processor	69	(1)
Tuning the Disk Subsystem	70	(5)
Tuning Memory	75	(3)
Optimizing Database Layout	78	(5)
Guidelines for Database Layout	78	(2)
Examples Using Files and Filegroups	80	(3)
SQL Server Configuration Options	83	(4)
AWE Enabled	84	(1)
Lightweight Pooling	84	(1)
Locks	85	(1)
Max Server Memory	85	(1)
Min Server Memory	86	(1)
Set Working Set Size	86	(1)
Recovery Interval	86	(1)
User Connections	87	(1)
Summary	87	(2)
New Features and Performance Enhancements	89	(14)
Extended Memory Support	89	(1)
Named Instances of SQL Server	90	(1)
Federated Servers with Distributed Partitioned Views	90	(5)
SQL Server 2000 Failover Clustering	95	(1)
XML Support	96	(1)
Database Maintenance Operations	97	(1)
Referential Integrity Enhancements	97	(1)
Full-Text Search	97	(1)
New Data Types	98	(1)
Index Enhancements	98	(1)
Administration Enhancements	98	(2)
Log Shipping	98	(1)
SQL Profiler	98	(1)
SQL Query Analyzer	99	(1)
Replication Enhancements	100	(1)
Additional Enhancements	101	(1)
Summary	101	(2)
Monitoring Performance with System Monitor	103	(18)
System Monitor	103	(7)
Viewing Performance Data in Real-Time Mode	104	(1)
Logging Performance Data	104	(6)
System Monitor Objects, Counters, and Instances	110	(10)
Processor Object	110	(1)
System Object	111	(1)
SQLServer: Buffer Manager Object	112	(1)
SQL Server: Databases Object	113	(1)
SQL Server: General Statistics Object	114	(1)
SQL Server: Latches Object	115	(1)
SQL Server: Locks Object	115	(2)
SQL Server: Memory Manager Object	117	(1)
SQL Server: SQL Statistics Object	117	(1)
Logical Disk and Physical Disk Object	118	(1)
Memory	119	(1)
Summary	120	(1)
Using SQL Profiler	121	(32)
SQL Profiler Overview	122	(1)
Using SQL Profiler	123	(21)
Setting Trace Options	124	(1)
Running Traces	124	(1)
Trace Templates	125	(7)
Modifying Trace Properties	132	(8)
Creating Trace Templates	140	(1)
Analyzing Profile Data	141	(2)
Using SQL Statements to Analyze Trace Data	143	(1)
Sample Scenarios	144	(2)
Looking for Long-Running SQL Statements	144	(1)
Looking for Resource Consumers	145	(1)
Using SQL Profiler to Detect Deadlocks	145	(1)
Using the Profiler as a System Log	145	(1)
Looking for Performance Problems	146	(1)
Debugging SQL Statements	146	(3)
Capture and Replay	147	(1)
Single-Stepping	147	(2)
Summary	149	(4)
PART II Sizing and Capacity Planning
Modeling for Sizing and Capacity Planning	153	(14)
Introduction to Sizing and Capacity Planning	153	(3)
Preconfiguration Capacity Planning	153	(1)
Postconfiguration Capacity Planning	154	(1)
History of Capacity Planning, Benchmarking, and Simulations	154	(2)
Modeling Principles for Sizing and Capacity Planning	156	(4)
Queuing Theory and the Knee of the Curve	156	(3)
Atomic Demand Modeling and Queue Modeling	159	(1)
Service Chains	159	(1)
Mathematics for Modeling	160	(5)
Understanding the Basic Model Set	160	(1)
The Utilization Law	161	(1)
Arrival Rates and Queues	162	(1)
End-to-End Response Time	163	(1)
Developing a Conceptual Model	164	(1)
Summary	165	(2)
Sizing the Database and Application Servers	167	(14)
Preconfiguration Capacity Planning	167	(5)
Transaction Processing Types	167	(2)
Peak Utilization vs. Steady-State Processing	169	(1)
Single-Tier vs. Multitier Systems	170	(1)
Page Faulting	171	(1)
Disk Requirements of the Database Server	172	(7)
RAID Fault Tolerance	172	(2)
Rules for Database Disk Drives	174	(2)
File Structures, Queries, and the Resulting I/Os	176	(2)
Sizing the Database Disk Farm	178	(1)
Sizing the Application Server	179	(1)
Single-Tier Model	179	(1)
Multitier Model	180	(1)
Summary	180	(1)
Methods for Sizing a System	181	(18)
Sizing the Processor	182	(5)
The Interview Process	182	(1)
Calculating CPU Utilization	183	(4)
Calculating System Statistics	187	(3)
I/O Throughput	187	(1)
System Capacity	187	(1)
Queue Length and I/O Response Time	188	(1)
Transaction Response Times	189	(1)
Sizing Memory	190	(3)
Sizing the Disk I/O Subsystem	193	(4)
Operating System Disks	193	(1)
Log File Disks	193	(1)
Database Disks	194	(2)
Sizing Example	196	(1)
Summary	197	(2)
Capacity Planning	199	(18)
Performance Tuning vs. Capacity Planning	200	(2)
Data Collection	202	(6)
Counters for All Machines	204	(1)
Counters for Database Servers	205	(2)
Counters for Web Servers	207	(1)
Counters for Application Servers	207	(1)
Service Chains	207	(1)
Data Analysis	208	(6)
Historic Data Reporting	209	(1)
Predictive Analysis	210	(3)
Correlative Analysis	213	(1)
Service Chain Reporting	214	(1)
Summary	214	(3)
PART III Configuring and Tuning the System
Online Transaction Processing Systems	217	(16)
What is an OLTP System?	217	(2)
System Configuration Options for OLTP Systems	219	(4)
Two-Tiered Architecture	220	(2)
Three-Tiered Architecture	222	(1)
Guidelines for Designing OLTP Systems	223	(6)
OLTP and Decision Support Workloads	223	(1)
Data Placement and Filegroups	223	(4)
Tuning OLTP Transactions	227	(1)
Controlling Data Content	227	(1)
Database Backup	228	(1)
Indexes	228	(1)
OLTP System Tuning	229	(3)
Processor Subsystem	229	(1)
Memory Subsystem	229	(1)
Pinning Tables in Memory	230	(1)
Page-Level and Row-Level Locking	230	(1)
I/O Subsystem	231	(1)
Summary	232	(1)
Data Warehouses	233	(16)
Comparing OLTP and Data Warehouse Systems	233	(2)
OLTP	233	(1)
Data Warehouses	234	(1)
Analyzing Data Warehouse Data	235	(1)
OLAP	235	(1)
Data Warehousing Tools	235	(1)
Designing a Data Warehouse	236	(9)
Tables and Schemas	236	(3)
Table Design Issues	239	(3)
Storage Design Issues	242	(1)
Cube Design Issues	243	(2)
Configuring a Data Warehouse for Performance	245	(2)
Hardware	245	(2)
Optimizing the Query Log	247	(1)
Summary	247	(2)
Tuning Replicated Systems	249	(26)
Replication Overview	249	(2)
Types of Replication	249	(1)
Replication Tuning Basics	250	(1)
The Distributor	251	(5)
Configuring the Distributor	252	(3)
Monitoring the Distributor	255	(1)
Tuning the Distributor	256	(1)
Tuning for Snapshot Replication	256	(7)
Attributes of Snapshot Replication	257	(1)
Configuring for Snapshot Replication	257	(5)
Monitoring the Snapshot System	262	(1)
Tuning the Snapshot System	263	(1)
Tuning for Transactional Replication	263	(7)
Attributes for Transactional Replication	263	(1)
Configuring for Transactional Replication	264	(5)
Monitoring the Transactional Replication System	269	(1)
Tuning the Transactional Replication System	269	(1)
Tuning for Merge Replication	270	(4)
Attributes of Merge Replication	270	(1)
Configuring for Merge Replication	270	(3)
Monitoring the Merge Replication System	273	(1)
Tuning the Merge Replication System	273	(1)
Summary	274	(1)
High-Performance Backup and Recovery	275	(20)
Backup and Recovery Concepts	275	(4)
Backup and Restore	275	(1)
Recovery	276	(1)
Non-Logged Operations	276	(2)
Types of Backups	278	(1)
Factors Affecting Performance of Backup and Recovery	279	(4)
The Backup Process	280	(1)
I/O Subsystem	280	(2)
Network	282	(1)
SQL Server	282	(1)
Configuring the System for Maximum Backup and Recovery Performance	283	(4)
Analyzing Performance Needs of a Backup	283	(1)
Configuring the Network	284	(2)
Configuring the I/O Subsystem	286	(1)
Backup Scenarios	287	(2)
Local Backups	287	(1)
Network Backups	288	(1)
Storage Area Networks	289	(1)
Backup Tips and Recommendations	289	(2)
SQL Server System	289	(1)
Network	289	(1)
Network Backup Server	290	(1)
General Tips and Guidelines	290	(1)
Review of Component Capacities	291	(1)
Summary	292	(3)
PART IV Tuning SQL Statements
Using SQL Query Analyzer	295	(22)
SQL Query Analyzer Features	295	(1)
Running SQL Query Analyzer	296	(3)
The Connection Process	296	(1)
The Query Window	297	(2)
Analyzing Queries	299	(1)
Execution Plans	299	(8)
Logical and Physical Operators	300	(7)
Interpreting Graphical Execution Plans	307	(2)
Example of Using SQL Query Analyzer	309	(4)
Index Tuning Wizard	313	(2)
Features of the Index Tuning Wizard	313	(1)
Limitations of the Index Tuning Wizard	314	(1)
Using the Index Tuning Wizard	315	(1)
Summary	315	(2)
Tuning SQL Statements and Stored Procedures	317	(22)
Application Design	317	(5)
Recommendations	318	(3)
Critical-Path Transactions	321	(1)
Using Stored Procedures	322	(7)
The Effect of Variables	327	(1)
Caching Execution Plans	328	(1)
Coding SQL Statements as Transactions	329	(1)
Efficient Retrieval of Data	330	(2)
Tuning SQL Queries	332	(4)
General Recommendations	335	(1)
Queries Using Joins	336	(1)
Prepared Execution	336	(2)
Summary	338	(1)
Using and Tuning Indexes	339	(20)
Index Fundamentals	339	(7)
Index Keys	341	(1)
Types of Indexes	341	(5)
Tuning Indexes	346	(12)
Choosing the Index Key Columns	348	(1)
Fill Factor	349	(1)
Using SQL Query Analyzer	350	(8)
Summary	358	(1)
Using Hints in SQL Server	359	(40)
Isolation Levels	359	(3)
Session-Level Isolation	361	(1)
SQL Hints	362	(7)
Join Hints	363	(1)
Table Hints	364	(2)
Query Hints	366	(2)
Bulk Copy Hints	368	(1)
Summary	369	(4)
PART V Appendixes and Glossary
A SQL Server Configuration Options	373	(16)
Configuration Options	373	(4)
Show Advanced Options	377	(1)
Option Specification	377	(1)
Changing Options with the Enterprise Manager	378	(11)
B SQL Server Monitoring	389	(10)
SQL Server: Access Methods	389	(1)
SQL Server: Backup Device	390	(1)
SQL Server: Buffer Manager	390	(2)
SQL Server: Buffer Partition	392	(1)
SQL Server: Cache Manager	392	(1)
SQL Server: Databases	393	(1)
SQL Server: General Statistics	394	(1)
SQL Server: Latches	394	(1)
SQL Server: Locks	394	(1)
SQL Server: Memory Manager	395	(1)
SQL Server: Replication Agents	396	(1)
SQL Server: Replication Dist.	396	(1)
SQL Server: Replication Logreader	396	(1)
SQL Server: Replication Merge	397	(1)
SQL Server: Replication Snapshot	397	(1)
SQL Server: SQL Statistics	397	(1)
SQL Server: User Settable	398	(1)
Glossary	399	(14)
Index	413