Getting Started

Getting Started

To troubleshoot performance and scalability problems, you first need to understand the application s database design. Assuming you are using SQL Server 2000, you should also have a good understanding of Transact-SQL (T-SQL); SQL Server internals, such as how the Query Optimizer chooses an execution plan; how data and indexes are stored; and how SQL Server utilizes the data and execution plan caches. To focus on identifying the bottlenecks, we will assume that you have already worked with SQL Server 2000 and are somewhat familiar with the built-in tools such as SQL Query Analyzer and SQL Profiler. Fortunately, there are many great books available that treat the necessary topics in depth. We have found the following to be good reference sources:

• SQL Server Books Online (installed as part of SQL Server 2000)

• Inside Microsoft SQL Server 2000 by Kalen Delaney (Microsoft Press, 2000).

• The Guru s Guide to Transact-SQL by Ken Henderson (Addison Wesley Longman, 2000).

• Microsoft SQL Server 2000 Performance Tuning Technical Reference (Microsoft Press, 2001).

In addition to learning as much as you can about SQL Server, you also need to be able to stress the SQL server at a production equivalent load level or anticipated load level. Often, load generated by a single user is insufficient to reveal scalability problems at the SQL tier. This is where Chapter 3, in which we introduced Microsoft Application Center Test as a Web stressing tool, is useful. By creating accurate stress scripts that reflect real-world scenarios in a testing environment, you can find the bottlenecks before they occur in production. If you determine that the solution to the problem requires index or query tweaks, you can then test your solution in the test environment for confirmation before applying the changes to the production environment.