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

The previous optimization notes were too summarized. Some friends suggested that I write the optimization steps and methods in detail, in this comparison, I will explain in detail how to use ANTS Profiler + SQL Server Profiler to find the bottleneck.

First, we need to deploy a test environment, copy the source code of the Web project to the Web server IIS in the test environment, so that we can directly access our website through IE. The SQL Server environment can be deployed on the same machine. if conditions permit, it is better to have a dedicated database test Server. After the test environment is deployed, make sure that no other users in the test environment are accessing the environment, so that the operations of other users do not affect us.

Assume that our website is slow when it is opened on the home page. It takes more than 10 seconds to open the website. When it is opened on the home page, multiple functions are called, and multiple stored procedures are called in the function, which function is slow? Which storage process is slow? Does the function execution on the Web server take a lot of time or does the execution of stored procedures in the database take most of the time? How long does each function and stored procedure take? These problems need to be solved through ANTS Profiler and SQL Server Profiler.

The process of using ANTS Profiler and SQL Server Profiler to locate bottlenecks is as follows:

(1) install and open ANTS Profiler on the Web server, and select the Profiler type as verbose mode in the Profiler Project Wizard ,:

(2) Click "Next" to display the application type to be tracked. Here, the project is published to IIS, so select the second one.

(3) Click "Next". The ASP. NET application configuration page appears. Set the application start page,. NET version, IIS version, and the port to be tracked.

(4) Click "Next" to go to the Code tracking selection page.. NET method. You can also select the first option to only trace methods with debugging files and source code.

(5) Here we want to track the Home Page. Once you click the "finish" button, the system will open the IE browser and load the Home Page. before clicking the "finish" button, you must enable SQL Server Profiler for the database in the test environment. SQL Server Profiler is responsible for tracking the script executed on the database. It is recommended to save the tracking result to the database, so that you can use SQL statements to find the tracking script. The configuration for saving the trace result to the database is as follows:

(6) For simple performance tracking, select RPC: Completed and SQL: BatchCompleted. As for the columns to be followed, focus mainly on TextData, CPU, Reads, Writes, Duration, and other columns. Do not worry about other columns. Use the default option ,:

(7) Click the "run" button in SQL Server Profiler to start tracking the database, and then click the "finish" button in the ANTS Profiler Wizard to enable tracking of ASP. NET applications.

(8) The system will open the IE browser and prompt you to enter a valid user name and password. After dozens of seconds, the home page will be displayed completely. SQL Server Profiler also traces a large number of SQL statements and stored procedures executed during homepage loading.

(9) Click "Get snapshot" in the ANTS Profiler toolbar. The system will be ASP.. NET application to create a snapshot, and then list the source code of the methods and methods with the longest execution time in the system from the start to the snapshot time ,:

(10) one of the longest time options is ViewMainQueryFGS. aspx. the Page_Load method in cs takes 13.27 seconds. The specific time spent is to call the BindTable method in the Page_Load method.

(11) Use VS to open the program source code, or in ANTS Profiler, click to view the BindTable method. We can see that there are two function calls in this method that are time consuming, one is 378 rows, it took 11.1 seconds, and 38 rows. It took 2.14 seconds ,.

(12) using the same method, you can see which methods are called by the GetDataListBySQL method and how many seconds each method has. We can see from the source code that this method finally calls the p_cx_prodplanfinish stored procedure in the data layer and switches to the SQL Server Profiler. We can see that it took 10.98 seconds for the system to call the stored procedure.

(13) Now let's look back at it. It took 13.27 seconds to load the entire page (the Page_Load method time), and it took 10.98 seconds to execute the stored procedure in light. Obviously, the bottleneck is that the Stored Procedure p_cx_prodplanfinish should be optimized first. In addition, another stored procedure is called in 2.14 seconds, which can also be optimized.

Using the same method, you can use ANTS Profiler and SQL Server Profiler to find out which function consumes the most time, time, stored procedure, and time. Determine whether the application consumes a large amount of time or the storage process consumes a large amount of time. Next, we can make a specific decision.

This article was completed half a month ago, but I haven't published it because of my next business trip. Now that my business trip is complete, I have time to post this article.