SQL Server disk request timeout 833 error causes and solutions_MsSql

This article mainly introduces the causes and solutions of the 833 error of SQL Server disk request timeout. Friends in need can refer to it

I recently encountered a SQL Server server that responded extremely slowly and client requests occurred. In the case of an error, an error message appears in the errorlog in the database that the disk request takes more than 15 seconds to complete.

For this kind of problem, is it a failure of the storage system or disk, a problem of SQL Server itself, or is it caused by the application? How to solve it?

This article will conduct a simple analysis of certain factors that cause this problem, but it cannot cover all potential possibilities, so specific analysis must be done when encountering similar problems.

Disk request timeout in SQL Server

The English version of the error message is as follows:

　SQL Server has encountered %d occurrence(s) of I/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/O is: %#016I64x

The Chinese version of the error message is as follows

SQL Server has encountered %1! times for database ID %4! The I/O request for file [%3!] took more than %2! seconds to complete. The operating system file handle is 0x%5!. The latest long-term I/O offset is: %6!

Refer to the 833 error message in the message information

for the specific 833 error Disk request timeout phenomenon

The specific error situation is as follows:

SQL Server has encountered m times I/O requests for file *** in database n that exceed 15 seconds. Just finished. The operating system file handle is ***. The offset of the latest long-term I/O is: ***

That means an error was encountered during the automatic growth of the database file.

.

What’s more interesting is that a DBA reported this error message to the engineer responsible for storage (SAN storage, not the attached disk), thinking it was possible The storage system is faulty or unstable.

The storage engineer believes that there is no problem with the storage. After checking the server, he said that the server is not normal and the memory is "almost full". For the database server, the memory is "almost full". "The situation can be said to be completely normal. Considering that the engineer responsible for storage is not a professional DBA and may not know much about the memory usage of the SQL Server database server, it is understandable to raise this question.

Because the storage used by the database server is high-performance SAN storage, the storage exists as a service and is used by multiple servers. There are no disk requests from other servers. It is unlikely that a certain If a server has a suspected "storage failure", it is simply regarded as a storage failure.

So what is the reason?

The meaning of database engine error 833

First let’s look at the specific meaning of this 833 error, so I won’t explain it myself. , it is written very clearly in that classic book.

In short, it means that when SQL Server requests disk reading and writing, it encounters a busy disk or other factors and has not completed it for more than 15 seconds.
For example, when reading and writing data, it needs to read and write data to the disk. When a request is initiated, but the disk is busy or there are other problems, it is too late or the response is not timely enough. This will undoubtedly seriously affect the response time of SQL Server's external server.

The above is a brief analysis, because this problem is not common, and the storage system is unlikely to have problems, then it is very likely to locate the factors of the current server itself.

Cause analysis

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

Actually, there were signs of this problem long before it happened. The server is usually quite stable (the CPU rarely exceeds 60%, the PLE of the memory can be stable for more than 20 minutes, the disk IO delay is low, etc. ), but occasionally there will be convulsions for a while

When the convulsions occur, the CPU will surge to about 80%, the PLE of the memory will be seriously reduced, and the IO delay will be seriously increased.

Now we can only start with Session of SQL Server. When observing the active Session in SQL Server, we found that the query time of a certain type of SQL statement is very long. Long,
Usually this type of SQL is executed relatively frequently within a certain period of time.

But under normal circumstances, the execution efficiency of this type of SQL is still relatively high. Why does it suddenly become very low?

When checking the corresponding execution plan of the active Session, we found that the waiting status of this type of active Session is IO waiting (PAGEIOLATCH_SH), and the execution of SQL is completely unexpected.

Because similar queries are executed relatively frequently, such sessions will be initiated from different clients. Once the SQL execution efficiency drops, a large number of active sessions will be backlogged on the server

Why does a SQL statement that normally executes well suddenly becomes very slow?

The reason is that at a certain point, SQL Server automatically triggers the update of statistical information, but this It is a relatively large table, but the sampling ratio for updating the default statistical information is not enough. If the sampling percentage is not enough, this statistical information is completely unavailable.

Once the automatic collection of statistical information is completed, it will issue a method it thinks is efficient (table scan instead of index seek) to the previous SQL statement based on the currently collected statistical information. In fact, this method is not It is reasonable,
This causes the corresponding SQL to use an unreasonable execution plan to implement the query, and at the same time causes Session congestion. The client sends a large number of Sessions and executes them slowly in an inefficient way. .

Therefore, the CPU will surge, the IO delay will increase, and the PLE of the memory will seriously decrease.

It is not difficult to understand from this. Dozens of querying Sessions are frantically making requests to the disk in an unreasonable way. The disk is busy with data requests from active Sessions and cannot respond because Automatic growth requests for data or index files cause the problem mentioned at the beginning.

Finally, it is solved through index reconstruction (promoting statistical information update, of course, pure statistical information update is also possible). For long-term prevention, it is necessary to arrange a job to artificially define the threshold value and sampling percentage of statistical information update.

Summary:

Problems on the database server, many problems are a chain reaction process, corresponding to some of the observed phenomena, very It may not be what it seems on the surface (disk request times out, is the problem in storage?)
A professional position must have professional qualities. For example, at first the DBA mistakenly thought it was a storage problem, and the storage engineer thought it was a server problem. It is abnormal that the memory is full, etc. In fact, it is not the root cause of the problem.
When faced with a problem, we must trace it back to its source and find out the most fundamental cause. This is the key to solving the problem.

The above is the detailed content of SQL Server disk request timeout 833 error causes and solutions_MsSql. For more information, please follow other related articles on the PHP Chinese website!