0

Sometimes queries that normally take almost no time to run at all suddenly start to take as much as 2 seconds to run. (The query is select count(*) from calendars, which returns the number 10). This only happens when running queries through our application, and not when running the query directly against the database server. When we restart our application server software (Tomcat), suddenly performance is back to normal. Normally I would blame the network, but it doesn't make any sense to me that restarting the application server would make it suddenly behave much faster.

My suspicion falls on the connection pool, but I've tried all sorts of different settings and multiple different connection pools and I still have the same result. I'm currently using HikariCP.

Does anyone know what could be causing something like this, or how I might go about diagnosing the problem?

Matt Sgarlata
  • 1,761
  • 1
  • 16
  • 13

2 Answers2

2

Do you use stored procedures or ad-hoc queries? On reason to get different executions when running a query let's say in management studio vs using stored procedure in you application can be inefficient cached execution plan, which could have been generated like that due to parameter sniffing. You could read more about it here and there are number of solutions you could try (like substituting parameters with local variables). If you restart the whole computer (and SQL Server is also running on it), than this could explain why you get fast queries in the beginning after a restart - because the execution plans are cleaned after reboot.

pevgeniev
  • 389
  • 2
  • 13
  • We use ad-hoc queries. Also when we do the restarts, we don't touch the database server, we are only restarting Tomcat, which is our application server software. We aren't restarting any physical machines. The query I'm running is ```select count(*) from calendars``` The query returns just the number 10. – Matt Sgarlata Sep 16 '14 at 20:00
  • I don't know if there could be some issue with long held connections, but you could try setting the ``maxLifetime`` property in the connection pool to something like 10 minutes. Also, the number of connections can affect the SQL server itself, so try limiting the connection pool to one or two dozen connections. – brettw Sep 17 '14 at 07:33
  • We actually already tried setting `maxLifetime` to 10 minutes but that didn't help. We are running on a database server with 32 processing cores and hyperthreading. We have tried connection pool sizes of 68, 130 and 5000 and all exhibit the same problem. The next thing we're going to try is taking a look at what's going on in the pool with JMX. We will have results at the end of September. (We can't run JConsole because this is a production box running at a secure DOD location). – Matt Sgarlata Sep 17 '14 at 17:44
1

It turned out we had a rogue process that was grabbing 64 connections to the database at once and using all of them for intense and inefficient work. We were able to diagnose this using jstack. We ran jstack when we noticed the system had slowed down a ton, and it showed us what the application was working on. We saw 64 stack traces all inside the same rogue process, and we had our answer!

Matt Sgarlata
  • 1,761
  • 1
  • 16
  • 13