I am responsible for a third-party application (without access to the source) running on IIS and SQL Server 2005 (500 concurrent users, 1 TB of data, 8 IIS servers). Recently, we began to observe a significant database lock (after several months of launching this application without problems). This occurs at random intervals throughout the day, approximately every 30 minutes, and each time from 20 to 100 sessions. All sessions eventually deleted the application time and terminated the sessions.
The problem disappears and then gradually reappears. The SPID responsible for blocking always has the following functions:
- WAIT TYPE = ASYNC_NETWORK_IO
- Running SQL: (@claimid varchar (15)) SELECT Claimid, enrollid, status, orgclaimid, resubclaimid, primaryclaimid FROM Claim WHERE primaryclaimid = @claimid AND primaryclaimid <> Claimid). A relatively harmless SQL that should return only one or two records, and not a large data set.
- NO OTHER SQL statements were involved in the lock, only that SQL.
- This is parameterized SQL for which the execution plan is cached in sys.dm_exec_cached_plans.
- This SPID has an object-level S lock in the claims table, so all UPDATE / INSERTs in the claims table are also blocked.
- HOST ID is changing. Different web servers are responsible for blocking sessions. For example, sometimes we return to web server 1, sometimes to web server 2.
When we return to the web server involved in the lock, we see the following:
- ,
-,
SQL.
- , ,
SystemOutofMemory. (
,
. ,
, .
?)
- -
SQL-.
( , , .)
:
- .
- .
- .primaryclaimid.
- .
- primaryclaimid, Claimid.
- .
- - .
- -.
, :
- - SQL
.
- SQL- SQL,
.
- -
.
- SQL-
-
.
- SQL Server,
X
( )
,
.
.
SQL Server / SQL?
ASYNC_NETWORK_IO?