Category list

Conclusion

Understanding how to profile and interpret the performance of managed code is key to building scalable and robust .NET Web applications. Profiling of managed code can be done using System Monitor and the .NET performance counters. This counter information can then be used in conjunction with any code instrumentation that you may be doing.

Chapter 8

Analyzing the SQL Tier

Among the most important uses of any business application are getting, saving, and displaying data. .NET applications are no exception to this rule, and having a properly analyzed and tuned SQL tier is critical to achieving high scalability. For applications with bottlenecks at the SQL tier, purchasing more or better hardware generally does not fix the problem. Before getting new hardware, you should first identify the bottlenecks affecting the scalability. The performance issues, for example, could be excessive I/O or processor utilization resulting from poorly chosen indexes or poorly written queries.

In this chapter, we will focus on how to detect SQL bottlenecks, and discuss some of the typical index problems that our team encounters frequently. The primary goal of this chapter is to share our experience as a performance analysis team at Microsoft. It is beyond the scope of this chapter to list every possible performance problem that you might encounter with the SQL tier. By sharing the methods we use to identify bottlenecks, we hope that you will be able to pinpoint your own problems. After you ve identified the problem, it is much easier to research the issue or seek help. For the purpose of demonstration, we will use examples based on the IBuySpy sample site; and if necessary, we will intentionally introduce problems. The results of examples in this chapter were obtained using a dual 1-GHZ Pentium III processor server with a 1-GB RAM system, running Microsoft SQL Server 2000 Enterprise Edition with Service Pack 2 and Windows 2000 Advanced Server with Service Pack 2.

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.

Flylib.com

Category list

Conclusion

Similar products