Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Other: HVR Not Closing SQL Connections

Answered
Dylan Janszen User

For your HVR product, you do not appropriately close unused spids to SQL Server. This causes significant bloat in monitoring tools. With the way connection pooling works, their should be no need to leave a connection open on the database server as this prevents other queries from utilizing those unused spids. SQL has a hard cap of 37,767 connections and if every application took the same approach as Fivetran, it would mean that no other applications would be allowed to connect to the database. This is typically a very easy thing to fix in languages like Python/C# (in my experience) by wrapping the database calls in a USING block. As soon as the query goes out of scope, the connection is closed and returned to the connection pool to be reused. This is not to be confused with disposing of a connection, which would require the application to reestablish a socket to the database, which can be very expensive/taxing on the application (Fivetran); I am NOT asking for this.

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Official comment
    Mark Van de Wiel User

    Thanks Dylan.

    I believe we keep the connection open in case we must run another query. For some setups this is required to "augment" the data e.g. in case of large character or binary value, depending on the method used to capture.

    Would you agree that if we sometimes augment data that it is better to keep a connection open? Or would you still say we should close it when possible, and then re-open if/when we have to fetch (augment) data?

    Thanks,
    Mark.

  • Mark Van de Wiel User

    Hi Dylan,

    You have a lot of lingering connections. How many channels do you have? Were you running a number of refresh and/or compare jobs when you retrieved this list?

    Thank you,
    Mark.

  • Dylan Janszen User

    Hello Mark!

    We currently have 7 or 8 channels in production. No refreshes or compare jobs currently running. 

     

    The issue is how Fivetran holds open its' connection within the connection pool it is using. Rather than closing the connection, it leaves it open. Please note that I am not suggesting Fivetran destroys the entire connection pool, but rather release it back into the pool to be reused. This should keep performance fast, but not leave a bunch of open threads out on the database server.

     

    If you create a Fivetran channel and point it to SQL Server and run sp_whoisactive (a very popular open source procedure that 99% of SQL DBAs use) you will see exactly what I mean. This procedure looks at DMVs like sys.dm_exec_sessions to grab open spids on the SQL Server. We probably run this command collectively ~50 times per day. Since Fivetran holds the connections open for every channel, our result set is bloated. Furthermore, these connections are consumed by the application and take away from SQLs maximum allowed conn count of 32,767. This isn't an issue per se, but if every application in our environment did this we may run into issues!

     

    Best Regards,

    Dylan