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

Today, I will talk to you about the causes and solutions of the 833 error caused by the SQL Server disk request timeout. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Recently encountered a SQL Server server response is extremely slow, and there is a client request error situation, in the database errorlog there is a disk request more than 15 seconds to complete the error message.

For such problems, is it a failure of the storage system or disk, or is it SQL Server's own problem, or is it caused by the application? How are we going to solve it?

Disk request timed out in SQL Server

The error message of the English version of the error is as follows:

SQL Server has encountered% d occurrence (s) of I is O requests taking longer than% d seconds to complete on file [% ls] in database id% d. The OS file handle is 0x%p. 0 The offset of the latest long I contract O is:% # 016I64x

The error message for the Chinese version is as follows

SQL Server has encountered 1! Secondary pair database ID 4! File in [% 3!] More than 2 Icano requests were made! It takes a second to finish. Operating system file handle is 0x%5!. The offset of the latest long-term Istroke O is:% 6!

Refer to error message 833 in message information

Specific 833 error application disk request timeout phenomenon

The specific error reports are as follows:

SQL Server has encountered m Icano requests for files in database n that took more than 15 seconds to complete. The operating system file handle is *. The offset of the latest long-term Istroke O is: *

In other words, an error was encountered in the process of automatic growth of files in the database.

.

What is interesting is that a DBA reports this error message to the engineer in charge of storage (SAN storage, not mounted disks), thinking that it may be caused by a failure or instability of the storage system.

The storage engineer thinks that there is no problem with storage, and after checking the server, he says that the server is abnormal and the memory is "almost full". For the database server, the situation of "almost full" memory can be said to be completely normal. Since the engineer in charge of storage is not a professional DBA, it is understandable to ask this question because the engineer in charge of storage is not a professional SQL Server.

Because the storage used by the database server is high-performance SAN storage, storage exists as a service, there are N multiple servers to use together, other servers do not have disk requests, it is not possible to say that a server will have a suspected "storage failure" is simply regarded as a storage failure.

So what is the reason?

The meaning of database engine error 833

First of all, let's take a look at the specific meaning of this 833 error, so don't pretend to explain it yourself. it's very clear in that classic book.

In short, it means that when SQL Server requests to read and write to the disk, it encounters a busy disk or other factors, and it needs to initiate a request to the disk after more than 15 seconds, such as reading and writing data, while the disk is busy or other problems, which will undoubtedly seriously affect the response time of the server provided by SQL Server.

The above is a simple analysis, because the problem is not common, the storage system is unlikely to have a problem, then it is likely to locate the current server itself.

Cause analysis

Because it is a dedicated SQL Server server, there are no requests from other applications, which are most likely related to requests made to the sqlserver database.

In fact, there was an omen long before this problem occurred. Servers that are usually stable (CPU is rarely more than 60%, PLE in memory can be stable at more than 20 minutes, disk IO latency is low, etc.), but occasionally there will be a frenzy.

When the CPU soars to about 80%, the PLE of the memory will drop seriously and the IO delay will increase seriously.

Now we can only start with the Session of SQL Server. When we observe the active Session in SQL Server, we find that the query time of a certain type of SQL statement is very long, and usually this kind of SQL is executed frequently in a certain period of time.

But under normal circumstances, the implementation efficiency of this kind of SQL is still relatively high, why suddenly become very bottom?

When checking the corresponding execution plan of the activity Session, it is found that the wait state of this kind of activity Session is IO wait (PAGEIOLATCH_SH), and the execution of SQL is completely unexpected.

Because similar queries are executed frequently, such Session will be initiated from different clients. Once the efficiency of SQL execution decreases, there will be a large backlog of active Session on the server.

Why do normally well executed SQL statements suddenly become very slow?

The reason is that at some point, SQL Server automatically triggers statistics updates, but this is a large table, but the sampling percentage of default statistics updates is not enough, and if the sampling percentage is not enough, this statistics is completely unavailable.

Once the automatic collection of statistics is completed, according to the currently collected statistics, it will send a way to the previous SQL statement that it considers efficient (table scan rather than index seek). In fact, this way is not reasonable, which causes the corresponding SQL to use an unreasonable execution plan to implement the query, and at the same time, it will cause Session congestion. A large amount of Session sent from the client is also being executed slowly in an inefficient way.

So CPU will soar, IO latency will increase, and memory PLE will drop sharply.

It is not difficult to understand that dozens of queried Session are frantically sending requests to the disk in an unreasonable way, and the disk is busy with active Session data requests, resulting in unable to respond to requests for automatic growth of data or index files, resulting in the problems mentioned at first.

Finally, it is solved by index reconstruction (promoting statistics update, of course, pure statistics update can also be done). For long-term prevention, it is necessary to arrange job to artificially define the threshold of statistics update and sampling percentage.

Many of the problems on the database server are a chain reaction process, and some of the phenomena observed are probably not what they seem to be (disk request timed out, the problem lies in storage? Professional position must be professional. For example, at the beginning, DBA mistakenly thought it was a storage problem, and storage engineers thought it was abnormal when the server was full of memory. In fact, these are not the root causes of the problem. In the face of the problem, to trace back to the source and find out the most fundamental cause is the key to solve the problem.

After reading the above, do you have any further understanding of the cause and solution of the 833 error caused by the SQL Server disk request timeout? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.