A database team suspects there may be an issue with a third-party ETL application opening, and closing, connections to a SQL server database repeatedly.
In terms of exact numbers — it opens about 30 connections, runs 30 queries in parallel, each query lasts 10 seconds, therefore 10 seconds later, the queries end, and each connection is closed by the application. There is about 10 seconds of other application logic, then the process starts again. So we're talking 30 connections opening and closing every 20 seconds.
This is also over a remote connection, and it would be the same IP address each time making the query.
Would this cause a crash, memory problem, DoS defense?
I for one thought that databases were set up to handle frequent connections and queries.
Anyway this 'loop' would happen about 1000-2000 times (so connections would be opening and closing for about 8 hours or so).
Is there anything in particular I should be monitoring at the database level or wherever else? Would this number of open/ close connections (even with a lightweight query) impact performance on a sufficiently robust database? What exactly should I be looking for?
A shared database on the same server started becoming slow/ laggy/ – though this began two weeks after this ETL process had completed running. I'm not sure there was direct evidence that this number of connections was the issue, but the DBA thinks it's the most probable suspect. I had mentioned it to him before because I was getting a SQL error message:
The semaphore timeout period has expired
I personally thought maybe this cryptic error meant there was rate-limiting going on. On second thought it may have been network connectivity, but it provided something to point at conveniently.
My question is what to look at, or monitor, or what evidence or data should be retrieved, to ascertain exactly if this process is causing performance issues? What questions should I ask in the ETL tool forum? I'm trying to figure out if this is a design problem in some regard and less connections should be opened at once, or if this database performance issue is actually completely unrelated.
Best Answer
Depending on how the server handles this, it might cause a potential problem. One technology that SQL Server implements to handle this kind of situation is "Connection pooling". With Connection Pooling, when the process closes its connection, SQL Server does not immediately remove the connection object from its memory. This way, if the process requests a new connection, SQL server can reuse the connection object instead of instantiating a new one. This process can be watched by monitoring the performance counters with perfmon. Here is a link to a MSDN blog for more information: http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/10/08/connection-pooling-for-the-sql-server-dba.aspx