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.
Analyzing the SQL
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
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
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