Why SQL SERVER 18056 has suddenly fallen in performance

The client cannot reuse a session with a SPID of%d, which has been reset for connection pooling. The failure ID is%d.

This error may have been caused by a previous operation failure.

Check the error log to find the failed operation that occurred just before this error message was displayed.

2011-11-19 16:33:53.91 spid5495 The client is unable to reuse a sessions with SPID 5495, which had

Reset for connection pooling. The failure ID is 1. This error may have been caused by a earlier operation

Failing. Check the error logs for failed operations immediately before this error message.

2011-11-19 16:33:53.93 spid2991 Error: 18056, severity: 20, Status: 1.

Case Description:

When the SQL Server's errorlog file in the constant error 10856, the CPU will be very low, at this time the SQL Client login database query operation is normal; the number of IIS connections soared, the Web site could not operate the database (such as login, basic query)

Analysis Prerequisite:

The problem is common, and there is no clear answer to the official explanation, which is that you either need to patch or you need to set up a connection pool for IIS.

The premise here is that the database has been patched up to date, the IIS connection database string is normal, the username and password are normal.

Analysis Process:

If the connection pool of IIS sets the 1500m,iis connection data to 1500 normal, the connection pool size of each session is averaged 1MB, and the database network packet defaults to 4096;

If there is a request to return 20M data at this time, then this session from the database to return the packet size will be more than the session to obtain the connection pool size, packet is 4096, than the normal request (Request 1M Reply) will require more packet delivery, This session corresponds to the time required to stay longer than the average level, under normal circumstances, these single large requests will not be too much problem.

If the number of IIS requests reaches 3000 at the same time, the average size of the connection pool per session is 0.5MB, and if the same 20MB data is returned, the session time will be longer!

If the client requests to return 100 30M of data at this time, then the request at this time, when the database is returned to IIS, IIS will find that the connection pool does not have enough memory space

When this session is assigned, the connection pool size of IIS does not increase automatically as the client request increases or the IIS server does not have more physical memory, and IIS

Because there is not enough connection pool space allocated to cache the corresponding session, but the subsequent client reply is still to IIS application, this time the problem comes!

IIS will release (or the IIS process down or IIS automatically restart) can not handle the session, when the database received the IIS-side sessions request query out data ready to return to

In the session of IIS, look for the SPID for the request and find that the SPID for the request no longer exists, but the TCP connection to the database does not immediately abandon the SPID because it does not exist

Data, this time the traffic of the network card will increase! At the same time the database errorlog is full of this error.

Solution:

0. First exclude DB If there is a deadlock

1. The most direct approach is to increase the size of the IIS connection pool

2. is to find the program in the large session request, modify the code

3. Limit the number of IIS processes, set the connection pool size based on day-to-day operation (not recommended, compelling)

4. Database-side limit SQL reply often: SQL Firewall or database limited long connection (not recommended, forced, no way)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/