By Tony Northrup
ASP.NET provides dramatically improved performance over earlier ASP-based applications. Although the standard configuration for ASP.NET applications provides much better performance than do older environments, administrators still have the opportunity to tweak the configuration and realize performance and scalability gains. This paper describes techniques that system administrators can use to tune the performance of applications built on the .NET Framework, as well as discussing .ASP.NET Web application, ASP.NET Web services, and .NET remoting applications.
|ASP.NET Applications and Web Services|
|Tuning Session State|
|Web Application Stress Tool|
|Other Applications Built on the .NET Framework|
|Internet Information Server|
Web applications and Web services deserve more performance tuning attention than any other kind of application. These applications are often externally-facing, and a poor Web site reflects poorly on a company to its customers. Public Web services targeted toward consumers have the potential of being visited by millions of users, which is in contrast to internally-facing applications. The potential user base for those applications is limited to the fairly predictable number of employees at the company.
ASP.NET gives system administrators a great deal of control over how Web applications and Web services perform and scale. The new session-state management capabilities separate information about a user's visit from the application completely, allowing administrators to choose whether the information is stored in a Web server's dynamic RAM, on a state server, or in a database. Built-in tracing capabilities enable administrators to isolate performance problems to sections of an application code—definitively pinpointing the problem to the application and not to a system configuration issue.
New ASP.NET-specific performance monitor counters provide detailed statistics about an application's performance, giving the administrator the information needed to resolve bottlenecks. Existing utilities, such as the Microsoft Web Application Stress (WAS) tool, can be used to generate traffic at a site and flush out performance problems before users see them. Because ASP.NET can be used for both Web applications and Web services, administrators have the same level of insight into the next generation of business on the Web. This section gives administrators step-by-step instructions for analyzing and tuning the performance of any type of ASP.NET application.
One of the biggest challenges with scaling Web applications and Web services to multiple Web servers is maintaining user state. No matter how a Web farm is configured, it is possible for a single user's requests to be forwarded to two different systems in a single session. If all the content on a site were static, this would not present a problem. However, most modern Web sites track a user's session to store user-specific information such as preferences, personalization, and shopping cart.
Previous versions of ASP offered developers access to session-state information and enabled developers to store information about a user within that session object. This information could be accessed from any other page on the site. However, ASP stored this information in the Web server's memory. Therefore, if multiple Web servers were used, and the user's request was sent to a different server than where their session originated, the session information would not be accessible. If a Web server were restarted, all session information would be lost, which could have a serious impact on a site—especially if the session is used to store a user's shopping cart.
ASP.NET provides traditional, single-server session information, very similar to previous versions of ASP. It also adds two methods for centralizing session information, making that session data available from many different servers in a Web farm or Web garden. The method an ASP.NET application uses is determined entirely by the system administrator—it is not coded into the application. Therefore, system administrators can scale ASP.NET Web applications and Web services that were not necessarily designed to be used in Web farms.
The three ways ASP.NET can be configured to store session state-information are in process, on a centralized state server, or in a SQL Server database. Storing session-state in process is similar to traditional ASP sessions because session-state information is stored in the Web server's memory and not shared between systems. This configuration offers the best performance because ASP.NET does not need to communicate across the network to retrieve session information. However, it limits scalability—sessions cannot span multiple servers, and if users move from one Web server to another, their session information is lost.
A state server is a central server running a specialized service included with the .NET Framework. State servers store state information for multiple servers in a Web farm. Users can move from one Web server to another without losing state information. State servers introduce some additional overhead because session information must be retrieved and updated using network requests from the Web server to the state server every time a user requests a page. Although user requests take a few additional milliseconds to fulfill, scalability and reliability are dramatically increased because the Web application can scale across multiple servers.
The third option for storing session-state information is to use a SQL Server database. This option offers the same benefit that a central state server offers—session tracking across multiple Web servers. However, using SQL Server to track sessions has more overhead than using the State Server service. SQL Server does have the capability of being clustered, providing ultimate redundancy. Also, SQL Server can scale to high-end hardware with more than four processors, enabling more sessions to be stored simultaneously.
The following sections discuss tuning performance for the three different methods of storing session-state information. There is one configuration parameter that all methods use but that does not affect performance. State can be managed in a "cookieless" manner, which includes state information as part of the URL instead of being stored in a cookie. ASP.NET inserts the session information into the URL of all links on a given page—and modifies only relative URLs. Therefore, cookieless state management will not work on sites that include absolute URLs in hyperlinks within an ASP.NET application.
Tuning In Process Session State. The "timeout" setting defines how long session state is maintained for a user after their last request. By default, this setting is set to 20 minutes. Therefore, if a user waits 20 minutes between sending requests to a server, a new session will be created. This does affect performance, particularly when in process sessions are used. The longer the timeout, the longer information stored in the session is maintained when a user is not actively visiting your site. However, session state maintained in process consumes memory on the Web server.
If your site has a small number of users who stay at the site for a long time, or visit periodically throughout the day, set a high timeout setting. If your site has thousands of users who typically view one or two pages before leaving, set a lower timeout setting. Then, monitor your Web server(s) to determine if maintaining session state is adversely affecting performance.
To tune the timeout setting, monitor the Sessions Active counter in the ASP.NET Applications object. This counter indicates the number of active sessions, and will, on average, grow as you increase the timeout setting. The maximum number of sessions that a Web server can handle varies depending on the amount of information applications store in the session. However, maintaining too many simultaneous sessions will consume server memory. Therefore, you should also monitor server-memory utilization. If memory paging increases after increasing the session timeout setting, you should either decrease the session timeout or add more memory to the server. A good counter overall measure of paging is the Page Reads/sec counter in the Memory object.
Session-state choice is defined in the machine.config or web.config files. By default, the <sessionState> section is defined in the machine.config file to as "InProc," which offers the best performance for Web applications running on a single Web server. The default settings in machine.config are:
sqlConnectionString="data source=127.0.0.1;user id=sa;password="
Configuring Session State with a State Server. When a state server is used to manage session state, the stateNetworkTimeout configuration setting is used to define the time, in seconds, that the ASP.NET Web application will wait for the state server to respond to network requests. By default, this time is 10 seconds. There are several reasons that a state server might not respond within that 10-second period—the state server is overloaded, the Web server is overloaded, or the state server is offline. If the state server or the Web server are experiencing periods of very high traffic (near 100% processor utilization) and session state is critical to the ASP.NET application, you can increase this timeout period to as much as 20 seconds.
Generally, the ASP.NET application will not send the Web-page results back to the user until after communicating with the state server. Therefore, if the state server is not responding, the end-user might be forced to wait for the network timeout to occur before receiving an error message. Increasing the stateNetworkTimeout can reduce the number of errors during extremely high-traffic periods; however, it also increases the time that users are forced to wait until the state server is offline. If your Web and state servers have timeout settings configured correctly, your application deals gracefully with session state errors. If your users are not patient enough to wait 10 seconds for a page to return, you might benefit from lowering the stateNetworkTimeout to 5 seconds. If your Web and state servers often have near 100% CPU utilization and session-state information is critical to your ASP.NET Web application, you might benefit from raising the stateNetworkTimeout to 20 seconds.
Monitoring the performance of a state server is accomplished by using the Performance console and the Event Viewer of the state server. Use the Performance console to monitor the State Server Sessions Active counter in the ASP.NET object. Also, monitor the processor utilization—both overall (% Processor Time in the Processor object) and for the session state (the aspnet_state.ex instance within the % Processor Time counter in the Process object). If the processor reaches 100%, requests from Web servers for session-state information will begin to queue, which will hurt the performance of your Web site. If the state server becomes so busy that requests timeout before being served, error events with IDs of 1072 or 1076 will appear in the state server's Application Event log. To remedy this situation, upgrade the processor of the state server.
To configure an ASP.NET Web application to use a central state service, add the following section to the application's <system.web> section of the web.config file. Pay particular attention to the stateConnectionString element, which defines the state server's IP address and port number—you should modify that attribute to match your state-server information. The sqlConnectionString attribute is not required, because that setting is used only when session-state information is stored at a SQL Server.
Configuring Session State with a SQL Server. Session state for a Web farm can be managed using a SQL Server, which is the best choice if session-state information is critical enough to require redundant servers. The ASP.NET State Service cannot be configured redundantly—only a single state server may be used, and if that state server is unavailable, ASP.NET Web applications will not be able to track user sessions. Monitor the performance of the SQL Server just as you would any other SQL Server. For more information about SQL Server performance, please read SQL Server 2000 Performance Tuning Technical Reference by Microsoft Press.
Note: If you choose to store session-state information in a SQL Server database, you should tune the ASPState database as described in the "SQL Profiler and Index Tuning Wizard" section of this document.
To configure an ASP.NET Web application to store state information in a SQL Server database, add the following section to the application's <system.web> section of the web.config file. You must modify the sqlConnectionString, which follows the standard connection string format, and you must at a minimum provide the name or IP address of the SQL Server and a valid username and password. To improve security, you should create an account with minimal privileges to the SQL Server instead of using the SA account.
sqlConnectionString="data source=SQLServerIP;user id=Username;password=Password"
The Microsoft WAS tool is a free utility from Microsoft used to generate load on a Web server. The tool simulates hundreds or even thousands of users simultaneously visiting your site and generates a summary report with performance information. By putting extra load on a Web server and monitoring the performance, you can determine how the application scales and what resource limits scalability. The WAS tool can be downloaded free of charge from the Microsoft Download Center. This section will describe how to use the WAS tool to establish a baseline for ASP.NET page rendering and to load test ASP.NET Web services. For step-by-step instructions about using the WAS tool, please read Performance Testing with the Web Application Stress Tool.
WAS can be used to test any type of Web server. Testing ASP.NET Web applications is not too different from testing any other Web application—but systems administrators tuning ASP.NET performance should pay particular attention to several key pieces of information in the WAS report. The Page Summary section of the WAS report lists all pages requested during the test, the number of times the page was requested, the Time To First Byte (TTFB), and Time To Last Byte (TTLB). The TTFB measurement can be used to determine the time needed for the Web server to render an ASP.NET page. To measure this time:
|1.||Install WAS on a computer connected to the same local area network as your ASP.NET Web server.|
|2.||Create a WAS script that queries each of the .ASPX pages on your site that you wish to measure.|
Select the script settings, and set both the Stress Level and Stress Multiplier to 1, as shown in Figure 1. This forces WAS to use a single client to request all pages, ensuring that multiple pages are not requested simultaneously.
Figure 1: Configuring WAS with a single client is an excellent way to create a baseline measurement of ASP.NET Web application performance.
|4.||Next, under Test Run Time, set the number of minutes to 5. Averaging requests beyond 5 minutes will give a reasonable number of sample points.|
|5.||Under Suspend, set the Warmup time to 1 minute to ensure that each page is requested several times before you begin taking measurements. ASP.NET pages are compiled upon first request—therefore, the first request is artificially long and should not be used for establishing a performance baseline. This warm-up period also enables any caching that may be enabled to take effect.|
|6.||Click the Run Script button on the toolbar. The script will execute.|
After the script has finished, click the Reports tab and select the new report. Scroll down to the Page Summary section, as shown in Figure 2. The TTFB number shown for each page is the time, in milliseconds, that your ASP.NET application takes to render each page under light load conditions.
Figure 2: TTFB Average measures the time required to render ASP.NET pages.
Measuring TTFB under light load creates a baseline. Comparing this number to the TTFB experienced under heavier loads gives you an understanding of how the Web application scales and how end-user experience will be affected. If the TTFB is greater than 1000 milliseconds (one second), the time required to generate ASP.NET pages might be affecting the end-user experience under normal traffic conditions. This time can be decreased by upgrading the Web server processors, tuning database access, and several other methods discussed in this paper.
While you have the WAS Report open, notice the Result Codes section of the report. The only result code listed should be 200—signifying that all requests were successful. Later, when you run this test against the server at higher loads, you will see errors listed as the ASP.NET application reaches its breaking point. To increase the load on your server, select Settings and increase the Stress Level and Stress Multiplier numbers. Increase these settings gradually and compare the results to your initial baseline to understand how site performance slows as traffic increases. To identify bottlenecks in your application, follow the monitoring guidelines in the "Identifying Bottlenecks" section of this document.
WAS can also be used to test ASP.NET Web services. By default, ASP.NET Web services generate viewable pages when the .ASMX file is retrieved by a browser. Users can enter information into form fields, and the request is processed as a call to the Web service. These automatically generated pages use the HttpGet Web service protocol, which is enabled by default for ASP.NET Web services. WAS can use this interface to simulate clients generating Web services requests, too, as shown in Figure 3. The easiest way to generate a WAS script for exercising a Web service is to use WAS to record these requests as you manually enter them with your browser and then delete any steps that do not include parameters after the ? in the request path. If your Web services clients use the HttpSoap method, the result is a less-than-perfect simulation. However, it is still a valuable way to identify performance bottlenecks.
Figure 3: WAS can be used to test Web services using the HTTPGet method by creating a script that requests the .ASMX files with the appropriate parameters.
See full-sized image.
Whether you use the Microsoft WAS Tool to artificially generate traffic, or you have a site that is already busy, it is important to monitor the Web server's performance under load. The best tool for monitoring the utilization of server resources is the performance console. In Windows 2000, launch the Performance console by clicking the Start button, navigating to Programs, selecting Administrative Tools, and clicking Performance. In Windows Server 2003, click the Start button, navigate to All Programs, select Administrative Tools, and click Performance. Table 1 gives you an overview of the most important performance counters for identifying bottlenecks in ASP.NET applications.
Table 1 Performance Counters for Identifying Bottlenecks
% CPU Utilization
The overall measure of total processor utilization on a Web server. The processor is the most common bottleneck on ASP.NET Web servers. If this counter peaks near 100% while the Web server is under load, you should add the % Processor Time counter for the Process object to isolate which process is bogging down the server.
% Processor Time
This counter provides similar information to the % CPU Utilization counter but identifies which specific process is using the most CPU time. To be certain you gather all the information you need, you should select the All Instances radio button in the Add Counters dialog when adding this counter. If the aspnet_wp process is consuming most of the processor, it is a good indication that rendering ASP.NET pages is the bottleneck. If the inetinfo process is to blame, IIS itself is the cause of the problem. These conditions can be remedied by upgrading the Web server's processor, add multiple processors, or by adding more Web servers. If your ASP.NET application is database-driven and you run a Microsoft SQL Server on the same system, you will very likely discover that the process named sqlservr is causing the CPU bottleneck. The best remedy for this situation is to move the SQL Server software to another physical server. Alternatively, upgrading the processor or adding more processors will help.
This counter measures the current rate of incoming ASP.NET requests and is a useful way to measure the peak capacity of your Web application while under load. The counter will report on only the number of requests for files with extensions configured in IIS to be passed to ASP.NET—most commonly, .ASPX and .ASMX files. To view the total number of requests, including requests for images, add the Get Requests/sec counter from the Web Service object instead.
This counter measures the current number of active ASP.NET sessions. A session is created by an ASP.NET application when a new user makes the first request. The session lives until: 1) the application explicitly abandons it when the user logs out, or 2) no requests are received from the user for the period of the session timeout. By default, ASP.NET sessions timeout after 20 minutes. This setting can be adjusted by modifying the timeout attribute of the sessionState element in the web.config or machine.config files. For more information about sessions and tuning the timeout value, please see the "Tuning Session State" section of this document.
Requests are queued when the time required to render a page is greater than the time between incoming client requests. In normal Web traffic, request rates are very erratic and queuing might occur for a few seconds during a busy moment. This will cause page load times to increase temporarily, but the queue is quickly eliminated during the next quiet moment. Traffic generated by a load-testing tool such as WAS might not have the same erratic patterns and might cause the ASP.NET Requests Queued counter to climb before it would do so under real traffic conditions. To simulate these random peaks and valleys in Web traffic, enable the Use Random Delay checkbox on the script settings page of WAS. If this counter still increases with this setting enabled, the server is currently above its peak capacity and a bottleneck should be identified and resolved before continuing testing. By default, ASP.NET is configured to queue a maximum of 100 requests. This limit is defined by the appRequestQueueLimit attribute of the httpRunTime element of the web.config or machine.config files.
After the ASP.NET request queue is full, new requests are rejected. This process is generally a good way for ASP.NET to behave under extremely heavy load because it is better to return an error to the user immediately and remove the request from the Web server's queue than to force the user to wait for their browser to timeout. Monitoring this counter gives you a running total of the requests received while the queue length was at the maximum.
ASP.NET Web applications and Web services include a very powerful ability to trace requests and responses. Although the tracing tool is primarily intended for developers troubleshooting bugs and refining application performance, it is also useful for system administrators who wish to give very specific feedback to developers about performance issues. Enabling tracing adds performance overhead and might expose private information, so it should be enabled only while an application is being actively analyzed. To enable tracing for an application, add or edit the following element to the application's web.config file, in the <system.web> section:
The key attribute that must be modified isenabled="true". If you are connected directly to the desktop of the Web server, set localOnly="true". This setting ensures remote users cannot access detailed information about your application. It is a potential security risk to expose trace information publicly; however, you can enable remote systems to view trace information by using the setting localOnly="false". The pageOutput="false" setting is the only safe setting for a production server; however, you can set pageOutput="true" on development servers to view trace information at the bottom of every ASP.NET page. The requestLimit="10" default setting specifies that only information about the first 10 ASP.NET requests will be stored. You may increase this number if necessary; however, you will also increase the overhead required by tracing.
When tracing is enabled for an application, you may request a special page from the application root: trace.axd. To view this page for the root application from a browser on the Web server, open the URL http://localhost/trace.axd. You will see the main trace page, as shown in Figure 4. Note that the trace.axd file does not actually exist in the root of your application—the request is intercepted by ASP.NET and processed dynamically.
Figure 4: Application tracing is a useful way of diving into the application logic to uncover performance bottlenecks.
See full-sized image.
When you click the View Details link for a request, you are presented with the Request Details page. This page presents very detailed information about the assembly of the ASP.NET page and its underlying components. The Trace Information section, as shown in Figure 5, shows how long each phase of the page rendering took to complete. Application developers can also insert application-specific information into this section, allowing administrators to identify performance problems within sections of the developer's code. In the trace information section, inspect the From Last(s) column to determine which phase took the longest to complete.
Note: By default, ASP.NET Web applications (.ASPX files) generate a great deal of useful trace information. Administrators can also trace ASP.NET Web services (.ASMX files), but the Trace Information and Control Tree sections appear empty unless the developer has explicitly built trace capabilities into the Web service.
Figure 5: The Trace Information section of the Request Details enables the administrator to narrow down problems within a specific page.
See full-sized image.
The next section in the Request Details page is the Control Tree, as shown in Figure 6. The control tree lists the name of every control called for a page, its type, the number of HTML characters that control generated, and the viewstate size bites. ASP.NET pages are typically assembled from many different controls. An ASP.NET control can be a table, a hyperlink, a toolbar, or anything else that can be contained in a Web page.
Figure 6: The Control Tree shows the details of every control in a given page.
See full-sized image.
The viewstate is a hidden field inserted into the HTML that ASP.NET uses to keep track of the page's current settings. Application developers often enable viewstate for controls that do not require it, which unnecessarily bloats a page—the control tree is useful for isolating those problems. Below the control tree, the request details page displays everything about the page and request. This information can be very useful for troubleshooting and detailed performance analysis.
Note: Don't forget to set enabled="false" in the <trace> element when you're done.
System administrators have a great deal of control over ASP.NET applications. Many aspects of an application's performance are configured through the machine.config and web.config files. For general information about configuring ASP.NET, please read http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;307626&. This section covers three configuration settings that can significantly affect ASP.NET performance: debug, the process model, and the data source configuration.
Disabling Debug. ASP.NET includes a special debug mode to assist developers with troubleshooting problems. Debug mode introduces overhead, reducing overall performance, and should be disabled on production ASP.NET Web servers. Debug mode is set in the opening <compilation> tag in the <system.web> section of the machine.config file and can be overridden by adding this section to the web.config file. When debug mode is disabled, the opening compilation tag should include the debug="false" element and might contain other unrelated elements, as shown here:
<compilation debug="false" explicit="true"
Configuring the Process Model. The <processModel> element in the <system.web> section of the machine.config contains several useful configuration settings when ASP.NET is running on an IIS 5.0 Server with Windows 2000. In Windows Server 2003 with IIS 6.0, these items are configurable from the Internet Information Services management tool. In a nutshell, the ASP.NET process model automatically recycles applications, reclaiming lost memory and resources. Further, it enables ASP.NET to be restricted to specific processors in a multiprocessor system. Most administrators will not need to modify this section of the machine.config file to get optimal performance. This section will cover the most useful configuration attributes for performance tuning.
Note: Most elements in the machine.config file can be overridden by placing the contents of the element in an applications web.config file. By default, the <processModel> element may be defined only in the machine.config file.
The default settings for <processModel> are:
Table 2 Attributes
|Attribute name||Default Setting||Description|
0xffffffff. This setting causes ASP.NET to launch a separate ASP.NET process for each processor on the Web server.
Used to limit ASP.NET to specific processors in a multi-processor system. It takes effect only when the webGarden attribute is set to false. Might improve performance on a system running both an ASP.NET application and SQL Server when the opposite cpuMask is used with SQL Server. To calculate the desired cpuMask, use the Calculator accessory in scientific mode. Set it to binary, and type a 1 for each processor in the system that ASP.NET should use and a 0 for each processor that should not be available. Then, convert the number to hex, and modify this element in the machine.config file. Be sure to start the number with '0x' to specify that you are using a hex number.
This setting causes ASP.NET to use the cpuMask to identify which processors will be used for ASP.NET. A true setting causes ASP.NET to use the Windows operating system scheduling mechanism.
When ASP.NET receives requests faster than it can respond to them, the requests are queued and dealt with when processing power is available. If the queue grows beyond a certain size, ASP.NET responds to the client with a "Server Too Busy" error. Depending on the application, sending this configurable error might be better than forcing the client to wait for a response.
Not set—handled dynamically by ASP.NET
The absolute path to a file that will be sent to users in the event of an error, such as when the requestQueueLimit has been reached. Administrators should use this setting to provide friendly error messages that notify them of problems and prompt them to try their request later.
Configuring Datasources. Some applications enable the administrator to define the data source. If an ASP.NET application allows for both OLEDB datasources and native SQL datasources, always choose the native SQL datasource when possible. ASP.NET's native SQL client provides more than 50% improved performance compared with connecting to a SLQ Server through a data source name (DSN) configured in the ODBC Data Source administrator. Unfortunately, there is no consistent manner for dictating how an application accesses data, so consult the application's documentation.
Applications designed to use the ASP.NET infrastructure give system administrators a great deal of control over tuning the performance of the applications. However, many applications will not use ASP.NET. For example, .NET remoting Web services do not leverage ASP.NET, and therefore, monitoring ASP.NET performance counters is not a useful way to analyze application performance. Further, .NET remoting applications do not support the HttpGet Web services protocol, so WAS is not a useful tool for generating load. Indeed, system administrators must rely on application developers to do the bulk of the performance analysis and tuning for .NET remoting applications. However, the Common Language Runtime (CLR) does expose several useful performance counters, as described in Table 3.
Table 3 CLR-Exposed Performance Counters
.NET CLR Remoting
Measures the current rate of incoming remoting requests. This counter gives the administrator a precise measurement of the current load on the application.
% Processor Time
This counter measures the percentage of the processor a specific process is consuming. .NET remoting applications each have a unique process name that matches the name of the application executable. Monitoring both the Remote Calls/sec counter and this % Processor Time counter will give the system administrator a good understanding of the processing time required per remote call.
.NET CLR Networking
Bytes Sent, Bytes Received
The Bytes Sent and Bytes Received counters measure network traffic generated by all .NET CLR applications, which is useful for measuring the amount of traffic an application generates. It does not include traffic generated by ASP.NET Web applications and Web services.
.NET CLR LocksAndThreads
Total # of Contentions, Current Queue Length
The number of contentions for all applications running within the CLR. Contention for system resources is normal for healthy applications. However, if this number rises during intermittent performance problems, it identifies an issue with a critical system resource being locked by a single application. This might be a sign that an application is using resources inefficiently, which can be corrected by the application developer.
.NET CLR Data
SqlClient: Current # pool and nonpooled connections
Measures the active number of SQL connections for .NET applications, including ASP.NET applications.
IIS is a fundamental part of ASP.NET Web applications, ASP.NET Web services, and many .NET remoting applications. Understanding the fundamentals of tuning IIS is a critical part of administering a high-capacity .NET application. These aspects of IIS tuning are not unique to ASP.NET, however, and a great deal of documentation already exists. Therefore, it will not be covered in detail in this document. For detailed information about tuning IIS, please read the white paper titled "The Art and Science of Web Server Tuning with Internet Information Services 5.0" located at http://www.microsoft.com/windows2000/techinfo/administration/web/tuning.asp.
Most applications built on the .NET Framework connect to SQL Server to retrieve data. In many circumstances, the database or the connection to the database becomes the performance bottleneck for the application. For data-driven applications, understanding SQL Server performance issues is critical for tuning the overall application. Application developers have a large amount of control over how data is retrieved from a SQL Server database, and the choices the developer makes have a tremendous impact on overall application performance. Although the specific database queries an application makes cannot generally be modified by the system administrator, you can analyze the types of requests a .NET application makes to the database and tune the indices accordingly. This section gives step-by-step instructions for tuning SQL Server indexes—a quick way to potentially dramatically improve an application's performance. For detailed information about SQL Server performance, please read SQL Server 2000 Performance Tuning Technical Reference by Microsoft Press.
An easy way to improve SQL Server performance is to use the SQL Profiler with the Index Tuning Wizard. The Profiler records queries as the SQL Server receives them and logs them to a file or a table in the database. The Index Tuning Wizard analyzes these files, identifies changes to the database design that would improve performance, and enables the system administrator to pick and choose which changes to implement. Running these tools adds overhead to the database and should not be done on a production system that is already near maximum capacity. To use the Profiler and Index Tuning Wizards to optimize your database design:
|1.||Log onto the console of your SQL Server system.|
|2.||Launch the Profiler by clicking the Start button, navigating to Programs, highlighting Microsoft SQL Server, and clicking Profiler.|
|3.||Create a new trace by opening the File menu, highlighting New, and selecting Trace.|
|4.||At the Connect To SQL Server dialog, select your SQL Server and authentication method, and click OK.|
|5.||At the Trace Properties dialog, select SQLProfilerTuning from the Template Name drop-list. Select the Save To File checkbox, and provide a new file name in the Save As dialog. Click Save.|
Click the Run button to begin recording requests sent to the SQL Server, as shown in Figure 7. If your application is currently active, let the profiler run long enough to collect at least 100 rows of data. If your application is not currently active, generate requests by using the application in a typical manner.
Figure 7: The Profiler gathers SQL requests for analysis by the Index Tuning Wizard.
After requests have been gathered, click the Stop button on the toolbar. From the Tools menu, select Index Tuning Wizard. The wizard will appear, as shown in Figure 8. Click the Next button, and select your database server when prompted.
Figure 8: The Index Tuning Wizard analyzes workload files generated by the Profiler and recommends changes to improve performance.
|8.||At the Index Tuning Wizard page, select the name of the database your application communicates with most from the Database list. If your application communicates with more than one database, you should repeat this process for each. Click the Next button.|
|9.||At the Specify Workload page, select the My Workload File radio-button. At the Open dialog, select the file you specified in step 5. Click the Open button, then click Next.|
|10.||At the Select Tables To Tune page, click the Select All Tables button. Click Next. At this point, the Index Tuning Wizard will attempt to identify index changes that would result in performance benefits. This might take several minutes.|
|11.||After the analysis, you will see the Index Recommendations page. If the Index Tuning Wizard found changes that would improve performance, they will be listed here, along with an estimation of the performance improvement. Generally, you can safely accept these changes. Click the Next button to continue.|
|12.||At the Completing the Index Tuning Wizard page, click the Finish button to close the wizard, and click the OK button to confirm when prompted. You may also close the SQL Profiler.|
System administrators have a great deal of insight and control over the performance of applications built on the .NET Framework. For example, ASP.NET includes session-state tracking capabilities that are separate from individual applications. These capabilities enable administrators to tune an application to meet the specific performance, scalability, and reliability needs of their environment. This paper has covered the details of how to monitor the performance of applications built on the .NET Framework, simulate busy conditions, and configure the major performance-impacting aspects of applications built on the .NET Framework. For more information about performance tuning, please read Improving .NET Application Performance and Scalability.