1

Configuration:

Hardware: HP ProLiant DL980 G7  
CPU: 4 Xeon X6550 @ 2.00GHz  
Memory: 393,206 MB  
SQL Version/Ed, OS: SQL Server 2008 Enterprise Ed (SP1) - 10.0.2757.0 (X64)  
Windows: Microsoft Windows Server 2008 R2 Enterprise

Recently our primary SQL Server (on a cluster) experienced an outage on a Monday morning. Over the weekend work had been done (to upgrade NIC drivers & firmware). The server failed over and rebooted and failed over again.

Everything seemed to be working just fine through Sunday; Clean logs for the light load of work & maintenance that is performed. However, on Monday morning, as application and DB activity ramped up the database experienced some blocking that caused connections to spike to the thousands. As the connections spiked there was memory pressure, the blocking got worse, and connections to the database started to fail and timeout. I was engaged while the blocking was peaking and tried to kill some SPIDs, but we decided to fail over to return to service more quickly.

My question is: could the RE-INDEX maintenance workload after the reboot fill the query plan cache and create memory pressure and blocking when the application workload resumes on Monday morning?

Our application service windows reflect operating hours, so we have a flood of activity as customer systems come online M-F 7am to 8pm. If the application timeouts lead to aggressive log-in attempts and a spike in database connections could we be creating a self-inflicted Denial-Of-Service on the database which leads to more memory pressure, lack of threads to do work and eventually bringing down the database?

Has anyone seen behavior like this before in large memory systems where application workload occurs in very specific windows?

The only two errors found occur 30 minutes after the application server reported connections failing:

Message: Error: 18056, Severity: 20, State: 29. 
The client was unable to reuse a session with SPID XXX, 
which had been reset for connection pooling. The failure ID is 29. 
This error may have been caused by an earlier operation failing. 
Check the error logs for failed operations immediately before this error message.
Improve this question

1 Answer 1

Reset to default
2

If the maintenance tasks were complete, then the resources would be free. If not, then you'll have blocking (unless you have online index rebuilds etc)

My random guess is some client command timeout, which issues an ABORT, which left locks and transactions. Your node failover is the same as closing all connections. The timeout could have been caused by cold cache, no cached query plans etc

Saying that, I have seen brain dead polling process overload a database by opening and closing connection 1000s of times per second. And similar. Or one client needs rebooted , normally indicated by ASYNC_NETWORK_IO waits.

Can you reproduce it though? Which will be tricky on a live system

See SQL Server Transaction Timeout for more (both answers)

Improve this answer
2
  • I can not re-create the issue. I have some limited performance and blocking details from Confio's Ignite tool. I can see sleeping spids causing blocking that led off the event.
    – Gabriel Cuevas
    Jan 30, 2012 at 20:20
  • @GabrielCuevas: a sleeping yet blocking SPID can be caused by a client timeout. If the connection goes back to the connection pool, it stays open in SQL Server keeping locks and transactions. Don't look at SQL, look at your client logs
    – gbn
    Jan 30, 2012 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.