patternsqlMinor
ASP.NET & SQL Server 2012 timeouts once a month
Viewed 0 times
once2012timeoutssqlnetmonthserverasp
Problem
We have an intranet site which works fine, except one day a month.
There are slow queries, unoptimized tables, tables with ~8 million records, etc. but daily usage is fine, although some functionality could be faster. The database server has 180GB RAM, 16 cores; it's a very fast server. The website is on another very decent server, with a very fast connection between.
Employees need to create a lot of invoices on the last day of each month and that's when it goes sour. On the last day of each month, people get a lot of time outs, a lot of pages which only run
While people are getting the timeouts, I turned on Activity Monitor, and the CPU is at 20%, there are 300 calls per second, which is really not much at all for that server. On daily usage it's 100-200 calls per second and it works fine. I ran sp_who2, sp_who3, they don't report any locks. I have also enabled deadlock report, to report anything for more than 20 secs and it doesn't report anything.
Some queries have 7000 different plans, although that's bad it doesn't explain timeouts. We have a lot of
We get very low physical reads, since RAM is 180GB, most of the data is in RAM, so slow disk is not the problem. Network is not the problem, since two servers (database server and web site server) have a very fast connection between them. We backup the database every 15 mins so every 15 mins I see a high I/O activity, but that happens on other days as well without a problem.
The only oddity I noticed was that when I ran
Do you reckon there's anything else I could check? The problem is we only have a few hours once a month when the time outs occur so we have very limited time to check what's going on, at other times it's OK. I wrote a JMeter script to reproduc
There are slow queries, unoptimized tables, tables with ~8 million records, etc. but daily usage is fine, although some functionality could be faster. The database server has 180GB RAM, 16 cores; it's a very fast server. The website is on another very decent server, with a very fast connection between.
Employees need to create a lot of invoices on the last day of each month and that's when it goes sour. On the last day of each month, people get a lot of time outs, a lot of pages which only run
SELECT FROM TABLE will fail to load because of timeouts.While people are getting the timeouts, I turned on Activity Monitor, and the CPU is at 20%, there are 300 calls per second, which is really not much at all for that server. On daily usage it's 100-200 calls per second and it works fine. I ran sp_who2, sp_who3, they don't report any locks. I have also enabled deadlock report, to report anything for more than 20 secs and it doesn't report anything.
Some queries have 7000 different plans, although that's bad it doesn't explain timeouts. We have a lot of
SELECT queries without a NOLOCK but that doesn't explain why pages with SELECT only time out. We get very low physical reads, since RAM is 180GB, most of the data is in RAM, so slow disk is not the problem. Network is not the problem, since two servers (database server and web site server) have a very fast connection between them. We backup the database every 15 mins so every 15 mins I see a high I/O activity, but that happens on other days as well without a problem.
The only oddity I noticed was that when I ran
sp_who there were 300 Waiting_Command state connections, that's much lower on other days.Do you reckon there's anything else I could check? The problem is we only have a few hours once a month when the time outs occur so we have very limited time to check what's going on, at other times it's OK. I wrote a JMeter script to reproduc
Solution
It might be related with leaking connections exhausting the connection pool.
If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following connection requests will end up waiting for a free connection in the pool, eventually timing out.
https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx
If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following connection requests will end up waiting for a free connection in the pool, eventually timing out.
https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx
Context
StackExchange Database Administrators Q#111407, answer score: 2
Revisions (0)
No revisions yet.