Microsoft SQL Server 7.0 Performance Tuning Technical Reference

by Steve Adrien DeLuca, Marcilin

• ISBN13: 9780735609099
• ISBN10: 0735609098
• Format: Trade Paper
• Copyright: 2000-03-23
• Publisher: Microsoft Pr

Summary

Understand the key mechanics and procedures needed to ensure the SQL Server 7.0 solution is most effective. Learn to implement better, faster business solutions built with the program, through easy-to-reference text and advice from experts.

Table of Contents

Tables	xiii
Acknowledgments	xv
Introduction	xvii
PART I Basic Concepts
Performance Tuning, Capacity Planning, and Sizing Overview	3	(8)
Performance Tuning and Optimization	3	(2)
Application Tuning	4	(1)
SQL Server Tuning	4	(1)
Hardware Tuning	4	(1)
Sizing and Capacity Planning	5	(1)
Server Tuning Methodology	5	(4)
Tuning Steps	6	(2)
Tuning Tips and Recommendations	8	(1)
Summary	9	(2)
SQL Server 7.0 Architecture	11	(20)
Memory Management	11	(5)
Physical and Virtual Memory	11	(1)
How SQL Server Uses Memory	12	(2)
Dynamic and Manual Memory Configuration	14	(2)
Using Additional Memory	16	(1)
Data Storage	16	(6)
Data Files and Log Files	16	(2)
Filegroups	18	(2)
Automatic File Growth	20	(1)
Pages, Extents, and Rows	21	(1)
Lock Management	22	(2)
What Are Locks?	22	(1)
Lock Granularity and Lock Modes	22	(1)
The locks Parameter	23	(1)
Thread Management	24	(1)
Transaction Log	25	(5)
Transaction Log Enhancements in SQL Server 7.0	25	(1)
How the Transaction Log Works	26	(2)
Truncating the Transaction Log	28	(1)
Checkpoints	28	(2)
Summary	30	(1)
Understanding the I/O Subsystem and RAID	31	(24)
Performance Characteristics of Disk Drives	31	(8)
Disk Drive Description	31	(2)
Disk Drive Behaviors	33	(2)
Disk Drive Specifications	35	(1)
Disk Drive Reliability	35	(1)
Disk Drive Performance	36	(2)
Solving the Disk Performance Capacity Problem	38	(1)
Introduction to RAID	39	(2)
Controller Caches	39	(1)
Internal vs. External RAID	40	(1)
Storage Area Networks	40	(1)
Common RAID Levels	41	(8)
Striping	41	(1)
Raid 0	42	(1)
Raid 1	43	(2)
Raid 5	45	(2)
Raid 10	47	(2)
Performance Comparison of Raid Levels	49	(3)
Read Performance	49	(1)
Write Performance	49	(1)
Disk Calculations	50	(1)
Choosing the Right Raid Level	51	(1)
I/O Latencies and SQL Server	52	(1)
Guidelines for Configuring I/O Subsystems	53	(1)
Summary	53	(2)
Tuning SQL Server	55	(22)
How to Measure Performance	55	(1)
Tuning the Server Hardware	56	(10)
Processor Architecture	57	(1)
Tuning the Processor	58	(1)
Tuning the Disk Drives	59	(5)
Tuning Memory	64	(2)
Optimizing Database Layout	66	(5)
Guidelines for Database Layout	66	(3)
Examples Using Files and Filegroups	69	(2)
SQL Server Configuration Parameters	71	(5)
Affinity Mask	72	(1)
Lightweight Pooling	72	(1)
Locks	73	(1)
Max Async IO	73	(1)
Max Server Memory	73	(1)
Max Worker Threads	74	(1)
Min Server Memory	74	(1)
Set Working Set Size	74	(1)
Recovery Interval	75	(1)
User Connections	75	(1)
Summary	76	(1)
New Features and Performance Enhancements	77	(16)
Dynamic Configuration	77	(5)
Memory	77	(2)
Procedure Cache	79	(1)
Locks	80	(1)
User Connections	80	(1)
Open Objects	81	(1)
Automatic File Growth	81	(1)
Automatic Database or File Shrinkage	82	(1)
Improved Performance and Other New Features	82	(10)
Locking	82	(1)
Transaction Log	83	(1)
Replication	83	(1)
Query Processing	84	(1)
Attaching and Detaching a Database	85	(1)
Checking Data Integrity	86	(3)
Backing Up and Restoring Databases	89	(3)
Summary	92	(1)
Monitoring Performance with Enterprise Manager and the Performance Monitor	93	(20)
Enterprise Manager	93	(2)
Monitoring Current Activity	93	(2)
Displaying Locking Information	95	(1)
Performance Monitor	95	(16)
Logging Performance Data	97	(1)
Monitoring Disk Activity	98	(1)
PerfMon Objects, Counters, and Instances	99	(6)
What Does It All Mean?	105	(6)
Summary	111	(2)
Using SQL Server Profiler	113	(34)
SQL Server Profiler Overview	113	(1)
Using the Profiler	114	(23)
Setting Trace Options	115	(2)
Running Traces	117	(1)
Predefined Traces	117	(7)
Modifying Trace Properties	124	(6)
Creating Trace Filters	130	(1)
The Manual Method	130	(2)
The Create Trace Wizard Method	132	(4)
Analyzing Profile Data	136	(1)
Sample Scenarios	137	(3)
Looking for Long-Running SQL Statements	138	(1)
Looking for Resource Consumers	138	(1)
Using the Profiler to Detect Deadlocks	139	(1)
Using the Profiler as a System Log	139	(1)
Debugging SQL Statements	140	(2)
Capture and Replay	140	(1)
Single-Stepping	140	(2)
Summary	142	(5)
PART II Sizing and Capacity Planning
Modeling for Sizing and Capacity Planning	147	(14)
Introduction to Sizing and Capacity Planning	147	(3)
Preconfiguration Capacity Planning	147	(1)
Postconfiguration Capacity Planning	148	(1)
History of Capacity Planning, Benchmarking, and Simulations	148	(2)
Modeling Principles for Sizing and Capacity Planning	150	(4)
Queuing Theory and the Knee of the Curve	150	(3)
Atomic Demand Modeling and Queue Modeling	153	(1)
Service Chains	153	(1)
Mathematics for Modeling	154	(5)
Understanding the Basic Model Set	154	(1)
The Utilization Law	155	(1)
Arrival Rates and Queues	156	(1)
End-to-End Response Time	157	(1)
Developing a Conceptual Model	158	(1)
Summary	159	(2)
Sizing the Database and Application Servers	161	(16)
Preconfiguration Capacity Planning	161	(5)
Transaction Processing Types	161	(2)
Peak Utilization vs. Steady-State Processing	163	(1)
Single-Tier vs. Multitier Systems	164	(1)
Page Faulting	165	(1)
Disk Requirements of the Database Server	166	(8)
RAID Fault Tolerance	166	(2)
Rules for Database Disk Drives	168	(2)
File Structures, Queries, and the Resulting I/Os	170	(3)
Sizing the Database Disk Farm	173	(1)
Sizing the Application Server	174	(1)
Single-Tier Model	174	(1)
Multitier Model	174	(1)
Summary	175	(2)
Methods for Sizing a System	177	(18)
Sizing the Processor	178	(5)
The Interview Process	178	(1)
Calculating CPU Utilization	179	(4)
Calculating System Statistics	183	(3)
I/O Throughput	183	(1)
System Capacity	183	(1)
Queue Length and I/O Response Time	184	(1)
Transaction Response Times	184	(2)
Sizing Memory	186	(2)
Sizing the Disk I/O Subsystem	188	(4)
Operating System Disks	188	(1)
Log File Disks	189	(1)
Database Disks	189	(2)
Sizing Example	191	(1)
Summary	192	(3)
Capacity Planning	195	(20)
Performance Tuning vs. Capacity Planning	196	(3)
Data Collection	199	(4)
Counters for All Machines	200	(1)
Counters for Database Servers	201	(2)
Counters for Web Servers	203	(1)
Counters for Application Servers	203	(1)
Service Chains	203	(1)
Data Analysis	203	(8)
Historic Data Reporting	204	(2)
Predictive Analysis	206	(2)
Correlative Analysis	208	(2)
Service Chain Reporting	210	(1)
Summary	211	(4)
PART III Configuring and Tuning the System
Online Transaction Processing Systems	215	(12)
What Is an OLTP System?	215	(1)
Guidelines for Designing OLTP Systems	216	(6)
OLTP and Decision Support Workloads	216	(1)
Data Placement and Filegroups	217	(3)
Tuning OLTP Transactions	220	(1)
Controlling Data Content	221	(1)
Database Backup	221	(1)
Indexes	222	(1)
OLTP Tuning	222	(2)
Memory Subsystem or Data Cache	222	(1)
Pinning Tables	223	(1)
Network Subsystem	223	(1)
Page and Row Locking	223	(1)
I/O Subsystem	224	(1)
Transaction Monitors	224	(2)
Summary	226	(1)
Data Warehouses and Data Marts	227	(10)
Definitions and Characteristics	227	(2)
Designing a Data Warehouse	229	(3)
Fact Tables	229	(1)
Dimension Tables	230	(1)
Star Schema	231	(1)
Snowflake Schema	231	(1)
Tuning a Data Warehouse	232	(3)
Hardware	232	(2)
RAID Levels	234	(1)
Database Layout	235	(1)
Summary	235	(2)
Tuning Replicated Systems	237	(26)
Replication Overview	237	(2)
Types of Replication	237	(1)
Replication Tuning Basics	238	(1)
The Distributor	239	(5)
Configuring the Distributor	239	(3)
Monitoring the Distributor	242	(1)
Tuning the Distributor	243	(1)
Tuning for Snapshot Replication	244	(6)
Attributes of Snapshot Replication	244	(1)
Configuring for Snapshot Replication	244	(5)
Monitoring the Snapshot System	249	(1)
Tuning the Snapshot System	250	(1)
Tuning for Transactional Replication	250	(7)
Attributes of Transactional Replication	251	(1)
Configuring for Transactional Replication	251	(5)
Monitoring the Transactional Replication System	256	(1)
Tuning the Transactional Replication System	257	(1)
Tuning for Merge Replication	257	(5)
Attributes of Merge Replication	257	(1)
Configuring for Merge Replication	258	(2)
Monitoring the Merge Replication System	260	(1)
Tuning the Merge Replication System	261	(1)
Summary	262	(1)
High-Performance Backup and Recovery	263	(18)
Backup and Recovery Concepts	263	(2)
Backup and Restore	263	(1)
Recovery	264	(1)
Types of Backups	264	(1)
Factors Affecting Performance of Backup and Recovery	265	(3)
The Backup Process	265	(1)
I/O Subsystem	266	(1)
Network	267	(1)
SQL Server	268	(1)
Configuring the System for Maximum Backup and Recovery Performance	268	(4)
Analyzing Performance Needs of a Backup	269	(1)
Configuring the Network	270	(2)
Configuring the I/O Subsystem	272	(1)
Backup Scenarios	272	(2)
Local Backups	272	(1)
Network Backups	273	(1)
Storage Area Networks	274	(1)
Backup Tips and Recommendations	274	(3)
SQL Server System	275	(1)
Network	275	(1)
Network Backup Server	275	(1)
General Tips and Guidelines	276	(1)
Review of Component Capacities	277	(1)
Summary	277	(4)
PART IV Tuning SQL Statements
Using SQL Server Query Analyzer	281	(20)
Features of SQL Server Query Analyzer	281	(1)
Running SQL Server Query Analyzer	282	(2)
The Connection Process	282	(1)
The Query Window	282	(2)
Analyzing Queries	284	(1)
Execution Plans	284	(5)
Logical and Physical Operators	284	(5)
Interpreting Graphical Execution Plans	289	(2)
Example of Using Query Analyzer	291	(5)
Index Tuning Wizard	296	(3)
Features of the Index Tuning Wizard	296	(1)
Limitations of Index Tuning Wizard	297	(1)
Using the Index Tuning Wizard	298	(1)
Summary	299	(2)
Tuning SQL Statements and Stored Procedures	301	(18)
Application Design	301	(3)
Recommendations	301	(2)
Critical-Path Transactions	303	(1)
Using Stored Procedures	304	(5)
Caching Execution Plans	308	(1)
Coding SQL Statements as Transactions	309	(1)
Efficient Retrieval of Data	309	(2)
Tuning SQL Queries	311	(4)
General Recommendations	313	(1)
Queries Using Joins	314	(1)
Index Tuning Recommendations	315	(1)
Prepared Execution	316	(1)
Summary	317	(2)
Using and Tuning Indexes	319	(18)
Index Fundamentals	319	(6)
Index Keys	321	(1)
Types of Indexes	321	(4)
Tuning Indexes	325	(10)
Choosing the Index Key Columns	327	(1)
Fill Factor	328	(1)
Using SQL Server Query Analyzer	329	(6)
Summary	335	(2)
Using Hints in SQL Server	337	(34)
Isolation Levels	337	(3)
Session-Level Isolation	339	(1)
SQL Hints	340	(6)
Join Hints	340	(2)
Table Hints	342	(1)
Query Hints	343	(2)
Bulk Copy Hints	345	(1)
Summary	346	(3)
PART V Appendixes and Glossary
A SQL Server Configuration Parameters	349	(14)
Setting Parameters	353	(1)
Changing Parameters with sp_configure	354	(1)
Changing Parameters with Enterprise Manager	355	(8)
B SQL Server Monitoring	363	(8)
SQLServer: Access Methods	363	(1)
SQLServer: Backup Device	363	(1)
SQLServer: Buffer Manager	364	(1)
SQLServer: Cache Manager	365	(1)
SQLServer: Databases	365	(1)
SQLServer: General Statistics	366	(1)
SQLServer: Latches	366	(1)
SQLServer: Locks	366	(1)
SQLServer: Memory Manager	367	(1)
SQLServer: Replication Agents	368	(1)
SQLServer: Replication Dist.	368	(1)
SQLServer: Replication Logreader	368	(1)
SQLServer: Replication Merge	368	(1)
SQLServer: Replication Snapshot	369	(1)
SQLServer: SQL Statistics	369	(1)
SQLServer: User Settable	369	(2)
Glossary	371	(14)
Index	385