29

When I profile my application using SQL Server Profiler, I am seeing lots of Audit Login and Audit Logout messages for connections to the same database. I am wondering, does this indicate that something is wrong with my connection pooling? The reason I ask, is because I found this in the MSDN documentation in regards to connection pooling:

Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool. This is because the connection is not actually closed when it is returned to the connection pool. For more information, see Audit Login Event Class and Audit Logout Event Class in SQL Server Books Online.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Also, does anyone have any tips for determining how effective the connection pooling is for a given SQL server? I have lots of databases on a single server and I know this can have a huge impact, but I am wondering if there is an easy way to obtain metrics on the effectiveness of my connection pooling. Thanks in advance!

Page
  • 9,945
  • 5
  • 37
  • 45
  • 2
    For anyone seeing this in 2018, since @Marcus Erickson's [answer](https://stackoverflow.com/a/468222/727345), the Microsoft docs have been updated and the incorrect line about pooled connections only logging once has been removed. – JonoB Dec 03 '18 at 11:31

2 Answers2

41

While the MSDN article says that the event will only be raised for non-reused connections, the SQL Server documentation contradicts this statement:

"The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool."

The best way to measure the effectiveness of pooling is to collect the time spent in connecting with and without pooling. With pooling, you should see that the first connection is slow and the subsequent ones are extremely fast. Without pooling, every connection will take a lot of time.

If you want to track the Audit Logon event, you can use the EventSubClass data column to whether the login is with a reused connection or a new connection. The value will be 1 for a real connection and 2 for a reused connection from the pool.application.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Marcus Erickson
  • 1,561
  • 1
  • 11
  • 10
  • When I create a sql profiler trace the EventSubClass column doesn't appear for the Audit Login and Audit Logout events....anyone know why? – Rory Jul 27 '09 at 12:46
  • 4
    There is a checkbox "Show all columns" when setting up the trace, EventSubClass then becomes an option on both Audit Login and Auddit Logout events. – rotary_engine Jan 22 '10 at 09:35
  • rotary_engine: what version of SSMS and SQL Server are you using? I can see the column EventSubClass, but it is not an option for "Audit Login". Thanks – motto Mar 11 '10 at 17:39
  • 1
    Okay I found what was missing: SP3 has to be installed, SQL 2005 RTM does not have the EventSubClass option available for "Audit Login". – motto Mar 11 '10 at 20:14
  • 1
    I learned something new about EventSubClass and new\reused connections. Thank you @Marcus Erickson – Ajit Goel Oct 20 '17 at 15:12
16

Remember that connections are pooled per connectionstring. If you have many databases and connect using many connectionstrings, your app will create a new connection when none exist with the correct connectionstring. Then it will pool that connection and, if the pool is full, bump an existing connection. The default Max Pool Size is 100 connections, so if you're routinely bouncing through more than 100 databases, you'll close and open connections all the time.

It's not ideal, but you can solve the problem by always connecting to a single database (one connection string) and then switch db context 'USE [DBName]'. There are drawbacks:

  • You lose the ability to specify a user/pass per connection string (your app user needs permission to all databases).
  • Your SQL becomes more complex (especially if you're using an out-of-the-box ORM or stored procs).

You could experiment with increasing the Max Pool Size if your database count isn't huge. Otherwise, if some databases are used frequently while others aren't, you could turn pooling off on the infrequent dbs. Both items are configured via connectionstring.

As far as metrics, monitoring the login and logout events on SQL Server is a good start. If your app is pooling nicely you shouldn't see a lot of them.

Corbin March
  • 25,526
  • 6
  • 73
  • 100
  • This clarified a lot for me. Thanks. Maybe you can help me with this one: What is the difference between the app bumping a pooled connection when reaching the max pool size vs. throwing an exception that "max pool size was reached"? – motto Mar 11 '10 at 15:56
  • @motto - you'll see the "max pool size..." exception when connections aren't getting closed. Even with a pool, you need to .Close connections to return them to the pool. An exception may prevent your .Close call from running if it's not in a "finally" or "using" block: http://blogs.msdn.com/tolong/archive/2006/11/21/max-pool-size-was-reached.aspx. Remember that some Command or Adapter calls use an implicit connection and need to be closed safely as well. Also, beware DB object creation in loops. – Corbin March Mar 12 '10 at 01:43
  • 3
    I'd also add that when performing "exec sp_reset_connection" the LOGIN/LOGOUT audit events ARE fired, even though the connection has not actually been disconnected. – Martin Feb 01 '12 at 16:02