Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the problem of accessing the home page caused by the timeout in the SQL statement. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Here's what happened with the malfunction.

A large number of the following errors were recorded in the log during the failure.

2020-02-03 06VOL37 Error 24.635 [Error] An unhandled exception has occurred while executing the request./Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareSystem.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -> System.ComponentModel.Win32Exception: Unknown error 258 at System.Data.SqlClient.SqlCommand.c.b__126_0 (Task `1 result)

The CPU consumption of the database server (Aliyun RDS SQL Server 2016 instance) has increased sharply.

The IOPS of the database server has exploded.

Through the CloudDBA of Aliyun RDS console, you can see that the SQL statement to obtain the home blog post during the failure was executed more than 30,000 times because the query timed out and the cache could not be established. Each request has to access the database.

After the failure was found, we returned to normal through the master / slave switching of Aliyun RDS.

After troubleshooting and analysis of the fault, the most suspected object is SQL Server parameter sniffing (see the blog post in the garden for details what SQL Server parameter sniffing is).

For this phenomenon caused by the reuse of execution plans generated by others, SQL Server has a proper term called "parameter sniffing parameter sniffing".

And we found the condition that caused the SQL Server parameter sniffing problem.

In our open api, we provide a web api to get the list of blog posts on the home page, but there is no limit on the maximum number of blog posts that can be obtained, that is, the following ItemCount parameter (the ItemCount value is 20 for all calls except open api).

SELECT TOP (@ ItemCount)

If someone calls open api and passes a large value to ItemCount, such as 20000, although the same SQL statement is called, due to the different values of ItemCount, SQL Server may generate an execution plan that varies greatly. For ItemCount 20000, the performance of the execution plan is good, and the performance of ItemCount 20 may be very poor. If the execution plan generated when querying ItemCount 20000 is cached and the query ItemCount continues to use this execution plan at 20:00, an otherwise good SQL query will suddenly become extremely poor performance. This is probably the reason for the failure we encountered today, and there is a problem with a SQL statement (which happens to cache a dissatisfied execution plan for this SQL query), and everything else is normal, which confirms this guess.

The lesson we learned from this failure is that we must limit the maximum values of ItemCount and PageSize in the code, which not only leads to unnecessary low-performance queries, but also may bring down the entire database because of SQL Server parameter sniffing problems.

This is the end of this article on "how to solve the problem of accessing the home page caused by timeout in SQL statements". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.