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!
Best Answer
While the MSDN article says that the event will only be raised for non-reused connections, the SQL Server documentation contradicts this statement:
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.