Microsoft SQL Server 2000 Optimization Guide

Description

• The step-by-step, up-to-the-minute guide to maximizing SQL Server performance and scalability!
• Plan SQL Server 2000/7 applications for maximum performance
• Make the most of Microsoft's SQL Server Profiler, Graphic Showplan, and Index Tuning Wizard
• Automate performance monitoring and notification
• CD-ROM: Extensive library of tuning tools, scripts, and practice resources!

Foreword by Andrew Scoppa, UCI Corporation

• A detailed, structured approach to identifying SQL Server performance bottlenecks—and resolving them
• Covers every component that impacts SQL Server performance: software and hardware
• Scalable, high-availability solutions for enterprise applications
• SQL Server 2000's enhanced architecture—and how it affects existing applications
• Techniques for automating performance monitoring and notification
• Extensive real-world scenarios and practical recommendations

Maximize the performance of all your SQL Server 2000/7.0 applications!

Microsoft SQL Server 2000 Optimization Guide delivers all the hands-on techniques and guidance you need to optimize any SQL Server 2000/7.0 application—no matter how large or business-critical! You'll learn a proven step-by-step methodology for tracking down SQL Server application bottlenecks and scalability problems—and resolving them.

Jenney Lynne Fields covers every aspect of optimizing both transaction processing and decision support applications. Discover how to install and configure SQL Server for maximum performance; create optimal indexes for each type of query; and write queries that take full advantage of the Query Optimizer.

Fields helps you identify opportunities for denormalizing logical database design to enhance performance; shows how to minimize contention and deadlock; and demonstrates how to work with stored procedures far more effectively. She also also presents in-depth guides to every key Microsoft database performance management tool, including the SQL Server Profiler, Graphic Showplan, and the Index Tuning Wizard.

Whatever your applications, whatever your role, this book will help you squeeze more performance and scalability out of the SQL Server databases you depend upon.

The accompanying CD-ROM contains an extensive library of practice optimization sessions, training materials, scenarios, scripts, and other resources for maximizing the performance of SQL Server 2000 and 7.0.

"A great resource! We can use this in our database practice."

— Dick Tarulli, vice president, Business Development and Marketing, Articulent Inc.

Sample Content

Downloadable Sample Chapter

Click here for a sample chapter for this book: 0130883581.pdf

Table of Contents

Foreword.


Preface.

I. OVERVIEW AND ARCHITECTURE.

Enhancements to SQL Server 2000. Large Memory Support. Internet Support. SQL Server V7.X Enhancements. Performance Analysis Tools. Resource Consumers. Key Considerations for Tuning SQL Server. Why Are Tuning and Optimization Necessary? Summary.

Introduction to the Storage Engine. Storage Engine Features. Subsystem Overview. Implementation Considerations. Data Organization. Storage Layout. Database Files. System Databases. Creating, Altering and Dropping Databases. Filegroups. Maintaining Data and Log Files. Table Organization. Data Types. Indexes. Managing the Transaction Log. Database Locking Mechanisms. Memory Utilization. Processor Utilization. Summary.

Introduction to the Query Processor. Query Execution. Query Optimization. Types of Query Optimizers. Statistics and the Query Optimizer. Improvements to the SQL Server Query Processor. Internet Support. Partitioning Views-Federated SQL Server 2000 Servers. Efficiency of Data Transfer. Query Governor. Query Optimization Techniques. Join Techniques. Hints. Indexing Strategies. Parallel Execution of Queries. Stored Procedures. Multiphase. Automatic Parameters. Transitive Predicates. Star Queries. Optimization for Data Modifications. Tools for Working with Queries. Summary.

II. PERFORMANCE CONSIDERATIONS.

Introduction. General Hardware Bottleneck Considerations. Disk Configurations. RAID or Not to RAID. Understanding RAID Levels. Hardware RAID vs Software RAID. Disk and Controller Considerations. Understanding Disk I/O. Transfer Rates. Disk Transfer Rates. Calculating Throughput. Determine Capacity of Controllers. PCI Bus Bandwidth. Tape Devices. Summarizing Disk I/O Subsystem Requirements. Suggestions for Improving Disk I/O. The More Hardware the Better. Read-Ahead Manager. Separate Activities. Disk Considerations for SQL Server and Windows NT/2000. Memory Considerations. Processor Considerations. Network Considerations. Summary.

Introduction to High Availability Solutions. Introduction to Clustering. Microsoft Cluster Server Implementation. Clustering Configurations. How Failover Works. SQL Server on Microsoft Cluster Server. Resource Issues Running SQL Server in a Cluster. Multiple Instances of SQL Server. Federated SQL Server 2000 Servers. Backup and Restore Capabilities. Log Shipping. Stand-By Server. SQL Server Replication. Summary.

Application Design Considerations. Normalization. Introduction to Indexes. Index Components. Clustered Indexes. Tables without Clustered Indexes. Clustered Index Example. Nonclustered Indexes. Example of a Nonclustered Index when a Clustered Index Exists. Example of a Nonclustered Index when a Clustered Index Has Been Dropped. Indexing Options. Creating Indexes. Optimizing Index Creation. Composite Indexes. Covering Indexes. Indexing Views. Index Selection. Understanding Index Selectivity and Density. Indexing Factors Affecting Query Performance. Tools for Managing Indexes. DBCC for Managing Tables, Indexes and Statistics. Summary.

III. DIAGNOSING AND RESOLVING PROBLEMS-TUNING AND OPTIMIZATION.

Monitoring Objectives. Create a Baseline. Windows NT/2000 Performance Monitor. Introduction to Performance Monitor. Performance Monitor Options. Monitor Each Key Area. Understanding CPU Utilization. Understanding Disk Utilization. Understanding Page Faulting-The Memory Object. Windows NT/2000 Objects-Process. Windows NT/2000 Objects-Thread. Windows NT/2000 Objects-Network. SQL Server Objects. SQL Server Default Performance Monitor Workspace File. SQL Server Object-Access Methods. SQL Server Object-Backup Device. SQL Server Object-Buffer Manager. SQL Server Object-Cache Manager. SQL Server Object-Databases. SQL Server Object-General Statistics. SQL Server Object-Locks. SQL Server Object-Memory Manager. SQL Server Object-Replication Agents Object. SQL Server Object-Replication Distribution Object. SQL Server Object-Replication Logreader Object. SQL Server Object-Replication Merge Object. SQL Server Object-Replication Snapshot Object. SQL Server Object-Statistics. SQL Server Object-User Settable Objects. Monitoring Performance with the Windows NT/2000 Control Panel. Virtual Memory. Application Responsiveness. Services. Network Bindings. Task Manager. System Information / Windows NT Diagnostics. Summary.

Introduction. Enterprise Manager. sp_configure. Monitoring Activity. sp_monitor. Viewing and Modifying Database Options. Managing the Configuration of SQL Server. Monitoring User Activity. Monitoring and Managing the Integrity of the Database. Summary.

Automating the Monitoring of SQL Server. Database Maintenance Plan. Jobs. Alerts. Creating Alerts. Summary.

Introduction to Server Profiler. Monitoring Events. Event Categories. Security Audit. Data Columns. Filters. General Trace Information. Sample Traces. Create a Trace Template. Create a Trace to Capture an SQL Server Workload. Reading the Trace File. Replay. Summary.

Introduction to the Query Analyzer. Execute SQL Statements and Commands. Object Browser. Color Coded for Ease of Use. Viewing Statistics, CPU Times, and Execution Profile. Performing Index Analysis. Create a Trace File from Query Analyzer. Analyze Data from SQL Server Profiler. ShowPlan and Graphical ShowPlan. Execute a Query with Graphical ShowPlan. Understanding Graphical ShowPlan. Performance Considerations. Interpreting Node Information. Reading Red Flags. Creating Missing Statistics. Managing Statistics. Managing Indexes. Re-Execute Query. Verify Successful Tuning Analysis. Summary.

Introduction to the Index Tuning Wizard. Using the Index Tuning Wizard. Select Tables to Tune. Index Tuning Recommendations. Analysis of Index Tuning Wizard Recommendations. Implementing Recommendations. Reviewing the Script File. Recommendations. Summary.

I. SYSTEM REQUIREMENTS. II. GETTING STARTED-LAUNCHING THE CD. Windows Users. Macintosh Users. III. NAVIGATING THE MEDIA EDITION-General Guidelines. IV SUPPORT.

Preface

Preface

Okay, so now you've gone out and purchased an application to run on SQL server. Well, it should be fast, right? Efficient, huh? No, what happened? Don't know? Well, this is the book for you. We will look at the strategies to use in defining the processes to tune and optimize applications running in the SQL Server environment. We will look at procedures that can be implemented when a project is first rolled out to help prevent performance issues.

Do any of these sound familiar?

After a fresh reboot of the server, the application runs great. As users get on the system and start utilizing the application, things start to slow down. By midday users start complaining about response time. What are you going to do? How are you going to troubleshoot the problem?

On a typical day, the server will start to slow down until performance comes to a halt, then performance returns to normal. How can this type of problem be diagnosed?

When we first bought the application, it ran great. Now it just seems to keep slowing down. I've added memory but it doesn't seem to help. Now what?

Understanding how to address response time and other performance degradation issues can be complicated and time consuming. The purpose of this book is to provide a resource to database administrators who find themselves faced with the responsibility of maintaining acceptable levels of performance for their SQL Server applications. It will look at defining processes as an attempt to prevent or at least be notified of problems before they occur. Once problems occur, we need to understand how to address them. We will interview SQL Server customers to discuss their problems and issues and, more important, their resolutions and suggestions for other customers. Finally, this book is intended to provide a real-life look at a structured approach to performance analysis and optimization, guiding the reader from problem to resolution.

One of the key differences between a successful implementation of any application and an unsuccessful one tends to lie in responsiveness and maintainability. If an application is slow to respond to the users and is equally difficult to manage for administrators, it has little to no practical usefulness in many organizations. Therefore the goal of this book is to provide readers with the knowledge and skills necessary to tune and optimize applications running on SQL Server 2000. It will evaluate the processes and procedures that are used in successfully tuning and optimizing SQL Server databases. For those already using SQL Server V7 the book will enhance your understanding of the architecture of SQL Server and how it impacts performance. It will act as an instructional guide on how to use the tools available with SQL Server 2000 to tune and optimize databases as well as how to continue to maintain an acceptable level of response.

There are three parts in this book. Part I provides an introduction to the features and capabilities of SQL Server 2000, and an insight into the architecture. Understanding SQL at the architectural level can provide the administrator great insight into managing applications running within the SQL Server 2000 environment. Both the storage engine and the query processor will be looked at in detail.

Part II discusses the factors to consider when implementing a solution based on SQL Server. Understanding what type of hardware and configuration of the hardware would provide an optimal SQL Server environment comes first. Additionally, the purpose for the application can drive other requirements such as high availability, implemented as either clustering or replication. There are also application issues to be considered, such as what are the best methods to implement to manage indexes and queries. An application that is not indexed properly and one who's data has grown may severely degrade performance if not maintained. Developing an understanding of the basics of indexing and interpreting queries can be very beneficial. For instance, it is important to be able to view the impact of issuing queries against the server and to determine if any obstacles, such as locks, have been placed preventing others to access the server.

Part III describes how to implement performance monitoring and analysis. It will guide the reader through creating a plan to address performance issues of both a strategic and of a tactical nature. Strategic performance analysis planning can work to eliminate many potential problems before they occur. It is critical to monitor and analyze your NT/SQL environment to understand how it is using resources. This section will build a plan for analysis that can be used as a tool to help prevent performance issues from occurring. The best strategy for performance tuning is to never have to do it. If you understand your environment, you can head off a lot of issues before they become problems. Further, it will discuss the tools and teach the user how to both use the tools as well as more important teach the reader how to interpret the results.

Updates

Submit Errata