. Net + SQL Server Enterprise Application Performance Optimization Note 2-find bottlenecks

In the previous article, I have briefly introduced the basic information of the Project. Today, I started to optimize the system performance. I am a newbie in terms of performance tuning. I don't have much experience and solid foundation, so I hope you can point out something wrong.

A bs system involves three roles: Web server, database server, and client. The first and most important step for performance tuning is to find the bottleneck. Is there a problem with the program on the Web server or with SQL query statements on the database server, or with HTML, JS, Flash, SilverLight, and images on the client? Even if you know which role has a problem, is it a CPU, memory, disk I/O, or network problem? If you don't find the bottleneck, you can start tuning. For example, if a page client request takes 10 seconds for the system to respond, it takes one second for network transmission and Browser display, it takes 8 seconds for the Web server to perform logic processing and operations, and 1 second for the SQL Server to query data. If an error occurs, the SQL Server determines the bottleneck as an SQL Server and optimizes the SQL query, if the query efficiency is improved by 100 times (only 0.01 seconds), the SQL optimization is a success, but it is only possible, the page after the client requests the optimization still takes 9.01 seconds. The user may not feel the difference between 10 seconds and 9.01 seconds at all, so the entire optimization fails.

We have mentioned three roles in the BS system: Web server, database server, and client. To find out which role is the bottleneck, the best case is that the three roles are three different computers, and the three computers are better than the others, that is to say, only one IIS is run on the Web Server, and no other services are run. Only SQL Server is run on the SQL Server, and only IE is run on the client, besides, IE only opens the system for optimization. Generally, most bottlenecks occur on the WEB server or SQL Server, and few on the client. (However, I have encountered a performance bottleneck on the client. Because an incorrect GIF image is used, the CPU usage of the client is 100%, making the user feel that the system response is slow .)

First, check whether the bottleneck is on the client. Check whether the system is slow only when the user uses the BS System in hardware and software environments? Is the system slow only when FireFox is used? Is CPU usage too high when users use this system? Through some investigation on the client, you can determine whether the bottleneck is on the client. If the client is used, it is necessary to optimize JS and HTML.

After confirming that the bottleneck is not on the client, the remaining two servers are available. To determine which server the problem is, the main tool used is the Windows counter. Before using Windows counters, you can use the Windows Task Manager to check CPU, memory, and process usage. However, it cannot be seen that the CPU usage of the database server is a problem with the database server, or the program running on the WEB server may be faulty. I have encountered such a situation before. Even during off-peak periods (after work, at night), the CPU usage of the database remains high. After a long analysis, I finally found the cause, it turns out that an endless loop is written in the program, and database operations are continuously performed. In addition, even if it is determined that it is a database problem, it cannot be considered that the operation is too complicated because of high CPU usage. In fact, it is more likely that there are too many IO operations on data. A large number of IO operations may increase the CPU burden.

In Windows counters, You can monitor the system's memory, CPU, disk, and counters provided by various applications (SQL Server, Asp. Net, and so on all have their own counters ). To monitor the system Memory, add Pages/sec under Memory. This counter indicates the paging interaction between the physical Memory and the virtual Memory on the hard disk. The larger the value is, indicates that the system reads and writes virtual memory frequently and the host is busy. The average value is generally below 20. There are also a large number of ASP. NET and SQL Server counters. I will not introduce them one by one.

If the environment permits, we can transfer the business system to the test environment, and separate the Web server from the SQL server, at the same time, it would be better to make the hardware configuration and network configuration in the test environment and production environment almost the same. Use LoadRunner or VS or other stress testing tools to simulate stress testing on pages with performance problems for multiple users, and enable related counters on the server. By monitoring the two servers, we can basically determine which server has a performance bottleneck.

If you want to obtain more detailed performance bottleneck information, you need to obtain the program source code, and then modify the source code to add the recorded time code in it, add the time recording code at page initialization, before Database Calling, after database calling, and after page Render. record the time and obtain the loading time of the page, the time it takes to call the database. For example, it takes 10 seconds to load the page. From the time record, it takes 9 seconds to call the database. This shows that the performance bottleneck is on the database, not on the Web server.

There is also a way to obtain the function call time, that is, use. net performance tracking tool ANTS Profiler, a tool developed by Red Gate, is very easy to use and introduced in my previous blog. Use this tool to Host the IIS on the Web server, and then open the IE browser to perform business operations, the tool can track the opening of a page, the time for calling each function during some page operations, and lists which function calls are time-consuming. While enabling ANTS Profiler for. net performance tracking, SQL Server should also enable SQL Server Profiler for database tracking. For example, for a query executed on the page, the system returns the queried table, and it takes 9.2 seconds to obtain the query operation through the ANTS Profiler trace, in SQL Server Profiler, we can see that it takes 8921 ms to query the database (Duration field). The entire operation is 9.2 seconds, and the database operation takes 8.9 seconds. Obviously, the bottleneck lies in the database.