Microsoft SQL Server 2000 Optimization Guide

Read an Excerpt

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

Read More