Just wondering if any of you has come up with something like this, or has a creative solution for this.
The idea is to detect which web requests are not closing the db connection.
So far, I have tried to create a Req Attribute (RA) to count the invocations of the method that does the closing. The method is called several times in a transaction if it opens several connections. In the purepaths list when I filter for this RA, it always has a value greater or equal to 1. Not very helpful.
Any ideas are appreciated.
What I normally suggest in this situations is use code quality solutions. One that I have used with quite success is SonarQube.
We have used Telegraf to collect the connection pool metrics for an application that uses sqlserver. We have metrics per connection pool, so we see connections in the pool and if there are increasing number of active connections, indicating that the app isn't freeing the connections.
DynaTrace won't keep track of DB connection as a garbage collection view, and monitoring connection close events is only one side of the story.
You might get a better picture monitoring both, open and close connection events at the same time, with two separate metrics. That way, you could compare them and realize not as many connections are being closed as opened. That would indicate the application is leaking DB connections into memory.
On a side note, several applications rely on database connection pools provided by 3rd party libs or the application server where they run. In those cases, the application is calling "open" and "close" methods for DB connections, but that may not necessarilt mean network connections events. Most of the time, the pool library will handle a constant number of network connections, while intercepting "open" and "close" events from the application. The application may think it's "opening" a db connection, but in reality connections are just handed in-and-out of a constant pool.
For such applications, you might get a better view of the situation by monitoring the database connection pool, and alert when it reaches max capacity.
Even in a pooled connection based architecture I have seen situations where the problem was simply that the worker thread was not 'returning' the connection to the pool. This can be tracked using explicit instrumentation of the getConnection() and closeConnection() methods. Then build a BT that reports these values on a per URL basis. The culprit might just pop out. Of course as stated earlier there's several iterations of this problem, and this technique might not catch them all. But with some creative instrumentation you should be able to track down the offending pattern.