December 17, 2008 at 2:22 pm
I am trying to Audit Login and Logouts on SQL Server to verify that our SQL Server Connection Pooling(ADO.net) is working. We currently have our application configured with a Min and Max number of connections set at 150.
I have configured my SQL Server side trace to monitor Security Audit Login and Security Audit Logout events (events 14 and 15). I am filtering on a specific Webserver hostname so that I can isolate this webservers database traffic.
I expect that when we fire up the webserver, we will see the 150 connections establish and nothing more.
What I am seeing is the 150 connections establish (Audit Login Events) immediately and then I see a serious of Audit Logout/Audit Login Events fire for each request. Quite a bit of chatter and possibly overhead. I do notice that the SPID does not get recycled. The same 150 SPIDs remain on the system.
Is this right?
The documentation does not seem clear on this topic...
Article http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx specifically states that "Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool."
This leads me to believe that we should not be seeing all of the chatter when a connection is fetched from the pool....
but then the BOL states...
"The Audit Login event class indicates that a user has successfully logged in to SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool."
My real concern is that I do not want to incur the overhead of the login/logout for each request, for obvious reasons. I have just taken over responsibilities as DBA for this server and one of the first things I noticed in their prior reports was the high logins per second. Diving a little deeper leads me to believe that the connection pooling is not working as I would expect.
Is this normal behavior for tracing connection pooling or could we have something set up incorrectly?
I would appreciate any help if possible.
thank you
February 16, 2009 at 12:19 pm
I also am dealing with a high number of logins per second in a .NET 2.0 Windows forms app. I've seen the reference you note that says pooling activity doesn't affect the login count. But here is a Microsoft reference that appears to say the opposite: http://msdn.microsoft.com/en-us/library/ms190260.aspx. I'm beginning to wonder if the high login count is not important when you're using connection pooling - but I haven't yet seen anything that confirms this.
March 20, 2009 at 1:59 pm
I actually worked with MS to answer this question.
This article should help:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply