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.