May 29, 2008 at 10:35 am
I am trying to fine tune an application in VB that access SQL Server 2005. After reviewing the SQL Profiler log there seems to be an excessive amount of Audit Login and then Audit Logouts. There are places where it executes several SQL Statements in a row without logging out and then there are places where it executes only 1 statement and then logs out. In both cases, the connection object is at the module level and execution never leaves that module after the call. By stepping through the code, it seems the logout seems to be triggered at the End Sub or End Function line. I believe that we are using OLE DB drivers to connect to the database and they are MDAC version 2.8.
I have read that a login is created when either a new connection is created or a connection from a connection pool is reused. So, my questions are these:
1) Is there any way to tell through the SQL Profiler whether or not the login is triggered from a connection pool versus a new connection object?
2) What things can I check to try and reduce/eliminate this seeming erratic behavior?
Thanks,
Mike
February 3, 2010 at 3:12 pm
michael.wiles (5/29/2008)
I am trying to fine tune an application in VB that access SQL Server 2005. After reviewing the SQL Profiler log there seems to be an excessive amount of Audit Login and then Audit Logouts. There are places where it executes several SQL Statements in a row without logging out and then there are places where it executes only 1 statement and then logs out. In both ...Thanks,
Mike
Have you had any response or come up with a solution?
I have a similar problem - except - mine is web access to SQL2005 via ASPX/ASCX and behaves nicely, like you and I want, but when the same calls are made to a SQL2008 instance of the same it does per this problem.
Can someone PLEASE help?
I cannot see a fundamental difference between SQL2005 & SQL2008 - so I can't tell what makes it NOT happen in 05 but does in 08 - again - HELP!!!!
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
February 3, 2010 at 3:22 pm
I don't recall ever finding a solution to the problem. We went on to fine tuning the application in other areas in ways that we do have control over. One strategy I've tried but don't remember how successful it was in regards to this particular area but was useful over all was creating a more global connection object. We then utilized that same open connection object from different parts of the application.
February 3, 2010 at 3:26 pm
Which was going to be my suggestion. As far as pooling, the connection itself is not closed, it is left open and if there is insufficient need then those open threads start getting dropped.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply