Microsoft SQL Server 2000 Optimization Guide

Microsoft SQL Server 2000 Optimization Guide

by Jenny L. Fields
  • 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


  • 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 writequeries 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.

About the CD-ROM

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.

Editorial Reviews

Provides strategies to use in defining the processes to tune and optimize applications running in the SQL Server environment, particularly addressing response time and other performance degradation concerns. The author overviews the server architecture, reviews hardware and application issues to consider before implementation, and describes how to implement database performance monitoring and analysis. The CD-ROM contains practice optimization sessions, training materials, and scripts. Annotation c. Book News, Inc., Portland, OR (

Product Details

Pearson Education
Publication date:
Prentice Hall Microsoft Technology Series
Edition description:
Product dimensions:
7.01(w) x 9.21(h) x 1.29(d)

Read an Excerpt



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 addressthem. 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.

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >