3

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 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 reproduce the problem but it's not the same as 300 people creating invoices at the same time. We're thinking of contracting a DBA but I'm not sure even if a DBA could do anything with what happens once a month for a few hours.

Any ideas?

Update:

  • I checked sys.dm_os_wait_stats every few minutes but I couldn't see anything out of the ordinary. But as @AaronBertrand suggested I'll look into that.
  • We use datareader and datasets but we don't do any heavy processing with the reader results.
  • It happens on the last working day of each month so it's a different day every month.
  • I'm new here and I was told this has been happening every month for the past 10 years at least. 10 years ago we probably had fewer employees but also slower servers.
  • There's nothing else that runs on the last day of each month.
  • We have 15 mins backups, report generation that runs every 15, every 30 minutes etc. but they don't cause a problem on other days.
  • Both datareader and datasets are used, but datareader iterations are used to get total of all items, etc., just quick loops, nothing like reading a file for each record.
  • There doesn't seem to be anything else running on either servers.
  • This happens on last working day of each month so it's a different day each month.
2

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

  • Yeah you might be right, good point. What would be a good way to prove this is the case? The Max Pool Size setting is 200. If I change that to say 500 and see that sp_who2 reports 500 connections that would verify this, yeah? I'm not saying changing the pool size is the fix but to prove that this is the problem. We'll have this issue again on Monday (31st) so I'll try to gather as much info as I can on Monday. – curious Aug 29 '15 at 10:27
  • Changing the Max Pool Size is a good idea. Addittionally I would collect some ADO.NET Performance Counters to grab more evidence of the problem (beware that some counters are off by default). Check the counter NumberOfReclaimedConnections. I would also collect NumberOfPooledConnections and NumberOfNonPooledConnections – Gonçalo V Aug 30 '15 at 13:39
  • I changed the pool size from 200 to 2048 and we didn't get any time outs. But I'm not sure if the new pool size fixed the problem. PooledConnections stayed under 100-120, NonPooledConnections stayed at zero all day as well as hard connections. Free connections stayed around 20-40. I was kinda hoping that PooledConnections would be over 200 which would explain why new pool size corrected the problem, not sure if my reasoning is correct. ReclaimedConnections is terrible, we had a total of something like 20000 reclaimed connections. GC numbers are pretty bad as well, the app needs a good look. – curious Aug 31 '15 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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