October 25, 2006 at 12:48 pm
I’m having a customer complain that there application is slow when accessing SQL Server. This application is a Access application that access a SQL Server back end using an ODBC connection. The ODBC connection uses SQL Server Authentication to connect.
Here is what I know. The Access application issues multiple login/logoff requests, one for each SQL Server command issued. Each login is taking very long (See attached profiler output for a single login). The length of time for each login seem to be associated with a single scan of the sysxlogins table in the master database. In the profiler output below the “Scan:Started” event starts at: 2006-10-23 08:49:21.850 and the “Scan:Stopped” event starts at: 2006-10-23 08:49:23.163. So this particular login took 310 ms. Now since the application issues 50-100 logins between each Access Window being displayed the customer is noticing quite a slow down. When SQL Server is functioning normally this single scan start and stop events would have the same StartTime.
This slow down occurs a few hours (0-24) hours after we start MSSQLSERVER service. We can get the slowness to go away by stopping and restarting MSSQLSERVER service. Of course this is not a solution to the problem, only a quick fix which is short lived.
Here are my questions:
1) What is SQL Server doing between the start of a “Scan:Started” event, and the start of a “Scan:Stopped” event? I would hope by knowing what occurs it might help us narrow down what might be causing this slowdown.
2) What else might I be able to monitor to determine what is causing this slowdown?
3) What tests might I run to further narrow down what might be causing this problem.
Another observation is Windows Authenticated logins are not slow, only SQL Server Authenticated logins.
So I’m looking for any suggestions on what might be causing this slow down. Just to let you know this slow down seem to start occurring after we applied some patches a few weeks ago. I’m tracing down those patches, and as soon as I have them I’ll post the patches numbers on this post.
Event Class StartTime EndTime DBID ObjectID
-------------- ----------------------------- ---------------------------- ------- ------
TraceStart 2006-10-23 08:49:03.973
Scan:Started 2006-10-23 08:49:21.850 1 33
Lock:Acquired 2006-10-23 08:49:21.850 2006-10-23 08:49:21.850 1 33
Scan:Stopped 2006-10-23 08:49:23.163 1 33
Lock:Released 2006-10-23 08:49:23.163 1 33
Scan:Started 2006-10-23 08:49:23.163 1 36
Lock:Acquired 2006-10-23 08:49:23.163 2006-10-23 08:49:23.163 1 36
Scan:Stopped 2006-10-23 08:49:23.163 1 36
Lock:Released 2006-10-23 08:49:23.163 1 36
Scan:Started 2006-10-23 08:49:23.163 1 36
Lock:Acquired 2006-10-23 08:49:23.163 2006-10-23 08:49:23.163 1 36
Scan:Stopped 2006-10-23 08:49:23.163 1 36
Lock:Released 2006-10-23 08:49:23.163 1 36
ErrorLog 2006-10-23 08:49:23.163 1
EventLog 2006-10-23 08:49:23.163 1
Scan:Started 2006-10-23 08:49:23.163 1 36
Lock:Acquired 2006-10-23 08:49:23.163 2006-10-23 08:49:23.163 1 36
Scan:Stopped 2006-10-23 08:49:23.163 1 36
Lock:Released 2006-10-23 08:49:23.163 1 36
Scan:Started 2006-10-23 08:49:23.163 1 36
Lock:Acquired 2006-10-23 08:49:23.163 2006-10-23 08:49:23.163 1 36
Scan:Stopped 2006-10-23 08:49:23.163 1 36
Lock:Released 2006-10-23 08:49:23.163 1 36
Audit Login 2006-10-23 08:49:21.850 1
1
Gregory A. Larsen, MVP
October 25, 2006 at 2:08 pm
Greg is every connection, or just once in a while? if it is just once in a while, could it be something as simple as the db AUTOCLOSE being set to ON, so that if no users use the db over a period of time, the db is closed to free resources, and then when someone tries to use the app again, the db needs to be reopened before you can login?
if that might be the case, im sure you know the command is ALTER DATABASE [whateverDB] SET AUTO_CLOSE OFF
I'm speculating because this is the kind of thing you see in Enterprise manager and you have 100 db's it takes forever to open because each db has to be opened before the db's can be shown;
i know if the db is hosted on a non-server version operating system, db's are set to autoclose by default when you create or restore them, regardless of whether they were backed up with the option set to OFF.
that might explain the situation you are seeing with the slow connection.
Lowell
October 25, 2006 at 2:21 pm
aww nevermind; you stated that it occurs every time; that shoots down the easy fix;
Lowell
October 25, 2006 at 6:59 pm
is the Access application connecting to the correct database initially? or is there the possibility that it is going through a series of try's to login before it gets to the database with the actual login? I'm not sure this makes sense; but I remember something along these lines that happened to me a long time ago.
btw, Lowel, did you know that you can "edit post" and within that screen, that you can "delete post"? lol... okay, so maybe you wanted to respond, even if it was wrong. Me, on the other hand, may decide my response is so stupid that I will come back tomorrow and delete it.
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 31, 2006 at 12:43 pm
David thanks for the thought. I'm sure they are logging on to the correct DB, but I'll ask them.
Gregory A. Larsen, MVP
November 1, 2006 at 1:20 pm
Here at the last two patches that where applied to this machine just before we started experiencing slow downs in the login process. Does anyone think either of these two patches could have caused the slow down in SQL Server logins via ODBC?
http://www.microsoft.com/technet/security/bulletin/ms06-055.mspx
http://www.microsoft.com/technet/security/Bulletin/MS06-049.mspx
Gregory A. Larsen, MVP
September 19, 2007 at 4:28 pm
Gregory did you ever find the problem? I am experiencing the same problem and I have a case in with Microsoft and they have yet to give a solution. The problem seems to worsen over days and I found that when I restart SQL Server that the duration times drop to ~15 milliseconds. However they creep back up to as high as 4000 milliseconds before I HAVE to restart SQL Server. I have seen it from several VB applications.
Robert
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply