. Net + SQL Server Enterprise Application Performance Optimization notes 3--SQL query statements

In the previous article, we used several methods to identify the bottleneck and find the bottleneck. Next, let's review it:

• LoadRunner stress test + Windows counter. This method is mainly used to find out which server has the approximate performance problem and which resource is insufficient.
• ANTS Profiler + SQL Server Profiler, the perfect combination of the two tools can accurately locate the function and SQL statement in which the performance is located.

If the performance problem lies in the program, it is necessary to adjust the functions in the program according to the business. This may be because there is a problem in writing the function and the algorithm is faulty. If this adjustment cannot solve the problem, in terms of architecture, should we use this technology and have any alternative solutions to implement the same business functions? For example, we assume that a function used to generate charts has performance problems, especially in stress testing. The original chart is generated based entirely on GDI +. On the Web server, the complex drawing is performed based on the data, and the drawn image is saved on the disk, then add the Img tag in HTML to reference the image address. Currently, using GDI + consumes a lot of memory and CPU, and the algorithm is not too big a problem. In this case, we need to consider modifying the architecture without using the GDI + drawing method, or asynchronous plotting. Since plotting consumes a lot of server resources, one solution is to transfer the plotting operation from the server to the client. Using SilverLight technology, a SilverLight file is downloaded only when a user opens a webpage. This file is used to call the Web Service of the Web server and obtain the data required for drawing, it is then displayed in the client drawing. In this way, the server only provides the WebService data access interface, and does not need to do the Drawing operation.

I will not list Optimization on. net for the moment. Today I will mainly talk about database optimization. Using ANTS Profiler + SQL Server Profiler, We can precisely locate the database scripts or stored procedures corresponding to a business operation. ANTS Profiler tells us that it takes 10 seconds to call a method, so we can use VS to open the source code, locate the put, and find the stored procedure of the corresponding call, here, a method may call multiple data layer methods and call multiple stored procedures. Write down the called stored procedures, and find the Duration used to call the stored procedure in the trace file of SQL Server Provider.

ANTS Provider traces the time of calling this method-SUM (Duration of all called stored procedures) = Time for logical processing in C # + time for data transmission between the Web server and the database server

In general, database servers and Web servers in enterprise applications or small applications are either in the same data center, the same LAN, or simply the same machine. In this case, the network transmission speed is fast, therefore, we do not consider the time above network transmission. Then we can conclude that:

Time for logical processing in C # = ANTS Provider traces the time of calling this method-SUM (Duration of all called stored procedures)

The time in the Code is obtained, and the time in SQL Server (that is, the Duration field) is obtained, you can determine the time spent by each Server on the page, in this way, we can find out whether the stored procedure or C # code is to be optimized. If it is a stored procedure, you can query the content in SQL Server Profiler to find which stored procedure consumes the longest time.

"The attacker first shot the horse and captured the thief first ." Multiple stored procedures are called. If the performance is on the database server, you must first optimize the maximum Duration of the stored procedure, there is also a large storage process of Reads. If Duration is large but Reads and Writes are not big, the possible reasons are as follows:

1. Resources related to this stored procedure are being occupied by other transactions. That is to say, it takes so much time to block the stored procedure. In this case, you only need to put forward the stored procedure and execute it several times to check whether the Duration is large but the Reads is not large.
2. The storage process itself is very complex, the T-SQL statement inside is 500 or 600 rows, the compiled execution plan is also a pile, which is a lot of logical judgment, a lot of function calls, in this case, tuning is more painful. In fact, this project I optimized this time is like this. The captured storage process is full of complicated logic, with less than 200 or 300 lines of code and more than 500 or 600 lines, there are also a large number of user-defined function calls. For this kind of stored procedure, I will write a blog to introduce how to optimize this project.
3. The program does not read much data, but requires a large amount of operations on the data. Hash join, aggregate function, DISTINCT, UNION and so on are all CPU-consuming. If this is the case, it depends on whether the index can be created or the rewrite method can be used for optimization.

We mentioned earlier that Duration is large and Reads is small. Of course, the more common case is that Duration and Reads are both very large. Then we will focus on how to reduce Reads. Many causes of Reads include the following:

1. No corresponding index is created. Query table t1 with the condition that where c2 = 'abc' returns the c1, c2, and c3 fields. In this case, if no non-clustered index is created for c2 (c1 is the primary key, A clustered index is created), then this query will perform a "clustered index scan". Originally, only a few records may be found, and all the records in the table will be scanned, naturally, Reads is high. The solution is to create an index. For example, you only need to create a non-clustered index for the c2 field, and then use the c3 field as the package row and column. If only the c2 field is used to create a non-clustered index, the search mentioned above will perform a "key search" after "non-clustered index search" to find the value of column c3, therefore, you must create a correct index.
2. Does not comply with the SARG principles. If the query does not comply with the SARG principle, it cannot be used even if an index is created. SARG is the meaning of the query parameters. I will not bother to explain how to write it in accordance with SARG. You can use Baidu. There are many related articles.
3. A large amount of business data is involved. That is to say, even if a correct index is created, the query conforms to the index used by SARG. However, due to the large amount of data involved, Reads is still large. In this case, you can no longer start with index and query, but you can only start with the database design. Whether appropriate redundant fields can be added to reverse normalization of the database, or if the real-time data requirements are not high, you can create an intermediate summary table and use SQL jobs to maintain this intermediate summary table, only the intermediate summary table can be queried. Or whether you can create an index view or calculate a column, and then create an index in the calculated column to perform a pre-operation to reduce the amount of data involved in the actual query.
4. Improper view is used. If the definition of a view is complex and involves many tables, this view is used during query, but only one or two tables in the view are used, querying a view may cause the system to query other tables that are not related to the query according to the view definition. Therefore, when using a view, you must know the definition of the view.
5. The user-defined function is incorrectly used. Hundreds of lines of code are stored in a stored procedure. For ease of writing, a user-defined Table value function is called in large numbers, and the function returns results only after complex queries and operations are performed. If the user-defined Table value function is called several or dozens of times, many complex queries and operations will be performed, and the Reads will naturally increase. The solution is to minimize the number of calls to this replication function. For example, after one call, the solution is stored in the table variables or temporary tables, then, use the table variable or temporary table.

If the Duration is not large, but the Reads query is large, it still needs to be optimized. Although it shows that the time consumed is not large, but due to the large number of Reads, it indicates that a large amount of IO is required. In the case of high concurrency, a large amount of IO processing will not increase the burden on the disk, as a result, CPU usage increases and performance decreases, and its Duration increases. The situations where Duration is not big but Reads is very big are still the situations mentioned above. You can create related indexes and modify query statements.