Connection Pooling vs Audit Login / Audit Logout Events

• jeffkretz

  SSC Eights!

  Points: 978

  More actions

  June 2, 2016 at 10:24 pm

  #400234

  I've recently been thrown a curve ball that threw into question my understanding of connection pooling, and how our application should be interacting with the database.

  I'm hoping to lay out my understanding (along with advice I was given from a SQL consultant), and hope that someone can help me reconcile this.

  The punchline is that SQL Profiler is showing over 500 Audit Login / Audit Logout events per second, and the consultant is saying this is too many, and that the ADO.NET layer of the application should be rewritten to not open and close a connection for each query, but should instead:

  1. Open a connection

  2. Run the queries related to the task

  3. Close a connection

  So, my first reaction was to shrug this off as bad advice. You cannot throw a stone without seeing an article talking about connection pooling, ADO.NET, closing your connection right away, yada yada yada. But I before I write this off, I want to make damn sure I don't have a misunderstanding of what is going on.

  Some background:

  a. The volume of queries (~500/sec) is as expected. This is a high-traffic web application.

  b. The typical query takes 1-50ms to run.

  c. Some take longer.

  Now, taking a closer look at the profiler trace, I see that the vast majority of Audit Login and Audit Logout events show "2 - Pooled" as the EventSubClass property. So it appears that connection pooling is being correctly used.

  So I guess my questions are:

  I. Is there an impact of having an "Audit Login (2 - Pooled)" | "Audit Logout (2 - Pooled)" wrapped around a query?

  My understanding was that there was effectively zero impact.

  II. The Server is a 64-core machine, with 1472 worker threads (the default for that configuration).

  Does this mean that only 1472 open and active connections can exist at one time?

  What happens if ADO.NET tries to open another connection?

  III. The application does have processing time in-between queries. Theoretically, this means that leaving the connection open for the duration of the request would result in more "blocking" of the other requests.

  Is there any flaw in this thinking?

  Thanks for any feedback on this matter.