Microsoft SQL Server 2000 Optimization Guide [Paperback]

Jenney Lynne Fields (Author)

Book Description

Publication Date: November 20, 2000 | Series: Prentice Hall PTR Microsoft Technologies Series

Maximize the performance of your existing SQL 7.0/2000 applications -- and optimize your future applications right from the start! This is the first book to provide step-by-step instructions for optimizing any SQL Server 7.x application -- and comprehensive insight into Microsoft SQL Server 2000's enhanced server architecture and powerful new features. Jenney Lynne Fields helps SQL Server 2000 DBAs and developers understand the impact of Microsoft's latest architectural changes on their existing applications. She reviews key up-front performance-related decisions, including hardware selection and indexing; and covers Microsoft's key performance-related tools, including SQL Server Profiler, Graphic Showplan, and the Index Tuning Wizard. Finally, Fields offers a detailed, structured approach to diagnosing and resolving performance problems. The book includes extensive examples and recommendations, including techniques for automating monitoring and notification when performance falls below specific thresholds.

Editorial Reviews

From the Inside Flap

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.

From the Back Cover

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

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.

See all Editorial Reviews

Product Details

• Paperback: 544 pages
• Publisher: Prentice Hall PTR (November 20, 2000)
• Language: English
• ISBN-10: 0130883581
• ISBN-13: 978-0130883582
• Product Dimensions: 9.2 x 7 x 1.3 inches
• Shipping Weight: 2.2 pounds
• Average Customer Review: 5.0 out of 5 stars  See all reviews (2 customer reviews)
• Amazon Best Sellers Rank: #3,113,847 in Books (See Top 100 in Books)

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

5.0 out of 5 stars Excellent guide, September 13, 2006

By 

Tim (TN USA) - See all my reviews

This review is from: Microsoft SQL Server 2000 Optimization Guide (Paperback)

I can only give 5 stars. The organization and content of the book is excellent.Author seems to be very knowledgeable.

5.0 out of 5 stars It certainly helped me thru my SQL 2000 tuning assignment!, June 14, 2001

By 

Kyle (Boston, MA) - See all my reviews

This review is from: Microsoft SQL Server 2000 Optimization Guide (Paperback)

Simply put, It is the best!!! I (SQL Server DBA) just took a SQL Server 2000 performance tuning assignment and after looking at several SQL Server 2000 books, this one is the best for performance tuning and optimization! It is easy to read, and it got just the level of details I need. It also guide the readers step by step when dealing with more advanced topics. I certainly learned about the critical performance counters to monitor on Windows and SQL Server, so now I can focus just on the counters I need. I surely learned a lot more details about the performance monitoring, SQL Server profiling, best hardware setup for high availability/scalability and optimal configurations!

The author did put in her own experience in the book, which helps when dealing with real world situation. I highly recommend this book for SQL Server DBA's out there!