Microsoft SQL Server 2000 Performance Tuning Technical Reference: Technical Refe

Edward Whalen, Marcilina Garcia, Steve Adrien DeLuca, Dean Thompson

Microsoft SQL Server 2000 Performance Tuning Technical Reference: Technical Refe
  • 出版商: MicroSoft
  • 出版日期: 2001-08-06
  • 售價: $2,010
  • 貴賓價: 9.5 折 $1,910
  • 語言: 英文
  • 頁數: 464
  • 裝訂: Hardcover
  • ISBN: 0735612706
  • ISBN-13: 9780735612709
  • 相關分類: MSSQL、SQL

    • 已過版

買這商品的人也買了...

相關主題

商品描述

Description:

he in-depth, practical guide to performance tuning, optimization, and capacity planning

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; and 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, and 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, plus how to take advantage of indexes and hints.

 

Table of Contents:

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

類似商品