SSMS: First connection to DBMS takes over 15 minutes

  • So I have come across an odd issue on my workstation that I was hoping someone on here might be able to provide some insight on.

    The issue I am having is that when I start a new instance of SSMS it is taking over 15 minutes to establish the first connection to a database engine. Subsequent connections on that SSMS instance work fine. Connections to SSAS and SSRS are not impacted. If I connect to a database engine using other tools they work just fine.

    This only affects SSMS and only occurs on the first connection to a database engine after that instance was start.

    I have tried re-installing, with no success.

    I have tried installing SQL 2014 SSMS and the issue occurs there as well.

    Not really sue what to try next to resolve this issue.

  • 1. Please check any anti virus is blocking sqlservr.exe.

    2. Check if there any .Net framework issues.

    3. Check CPU Utlization when you open SSMS.

    4. Run the profiler trace if needed.

  • since you are restarting the instance, and it takes a long time, i'd say it's because the databases are not coming on line quickly.

    the first thing i would check is how many virtual log files the databases have; that's a classic symptom of having a zillion VLF's.

    my general rule of thumb would be "less than 100" is my goal.

    on each database, including master, run this command

    DBCC LOGINFO

    if you see a large number of rows, ie hundred or thousands, that's what i'd consider cleaning up.

    this command would generate the command to run:

    select 'DBCC SHRINKFILE(' + name + ',TRUNCATEONLY);' + CHAR(13) from sys.database_files where type_desc='LOG'

    run the commadn that is generated, and run DBCC LOGINFO to see it's effect.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (1/8/2016)


    since you are restarting the instance, and it takes a long time, i'd say it's because the databases are not coming on line quickly.

    the first thing i would check is how many virtual log files the databases have; that's a classic symptom of having a zillion VLF's.

    my general rule of thumb would be "less than 100" is my goal.

    on each database, including master, run this command

    DBCC LOGINFO

    if you see a large number of rows, ie hundred or thousands, that's what i'd consider cleaning up.

    this command would generate the command to run:

    select 'DBCC SHRINKFILE(' + name + ',TRUNCATEONLY);' + CHAR(13) from sys.database_files where type_desc='LOG'

    run the commadn that is generated, and run DBCC LOGINFO to see it's effect.

    Sorry, poor choice of wording, no database engine instances are being stopped or started. By instance I meant a new SSMS window. So for example, I start my laptop, when I open my first SSMS window the first connection to ANY database engine will take 15 minutes to connect, if I start a second database engine connection in that window it will connect right away. If I open a SECOND SSMS window the first connection in that window to ANY database engine will again take 15 minutes and subsequent connections will work normally.

  • cmc123 (1/8/2016)


    1. Please check any anti virus is blocking sqlservr.exe.

    2. Check if there any .Net framework issues.

    3. Check CPU Utlization when you open SSMS.

    4. Run the profiler trace if needed.

    1) I have checked anti-virus and checked the anti-virus logs and not found anything there

    2) I did a repair on .Net framework, which didn't help. I haven't been able to identify any issues

    3) CPU utilization is minimal

    4) From what I can tell SSMS is not hitting the SQL Server until immediately before the connection is actually established. It seems that the hang-up is on my end.

    Thanks

    Jim

  • is the sql instance you are trying to connect with on your local Pc or remote server?

    has this issue started just recently or always been "slow"

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • if you connect via IP address instead of server name, is it near-instant? maybe it's still networking/dns?

    how about fully qualified instead of WINS name?

    ie connecting to 10.40.10.100 or SQLProd.Yourdomain.local or whatever it actually is.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (1/8/2016)


    if you connect via IP address instead of server name, is it near-instant? maybe it's still networking/dns?

    how about fully qualified instead of WINS name?

    ie connecting to 10.40.10.100 or SQLProd.Yourdomain.local or whatever it actually is.

    @j-2 Livingston SQL - It started happening maybe a month ago, before than it was not slow. I don't have a local instance on my workstation, but it happens with any remote instance.

    @lowell - I hadn't tried that before, but I just tried and the response seems to be the same in both cases.

    Thanks

    Jim

  • I should also mention that this occurs when using both Windows Authentication AND SQL Authentication

  • Might be AD issues with account you are trying to logon.

    You need to check DNS replication

    As you mentioned, it was happening after every restart, That means it was tying to validate your account.

    Check if SPN is configured

    Also, You need to verify SQL Service account has permissions on AD.

    Guessing it might be AD level issues.

  • farside41 (1/8/2016)


    Lowell (1/8/2016)


    if you connect via IP address instead of server name, is it near-instant? maybe it's still networking/dns?

    how about fully qualified instead of WINS name?

    ie connecting to 10.40.10.100 or SQLProd.Yourdomain.local or whatever it actually is.

    @j-2 Livingston SQL - It started happening maybe a month ago, before than it was not slow. I don't have a local instance on my workstation, but it happens with any remote instance.

    @lowell - I hadn't tried that before, but I just tried and the response seems to be the same in both cases.

    Thanks

    Jim

    ok...not sure if I can help, but I do remember having a similar issue a few years back with laptop connection to a SQL 2005 instance....cant remember the solution sadly

    was something to do with internet validation and "crl.microsoft.com"....of course this may may not apply to you.

    if it was "working" and now is slow....can you detail what has changed ?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • this thread seems to have the same issue:

    DNS

    http://dba.stackexchange.com/questions/104378/sql-server-management-studio-slow-connection-or-timeout-when-using-windows-authe

    Certificates?

    http://superuser.com/questions/7247/why-does-it-take-so-long-for-sql-management-studio-to-connect

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • After successfully logging in, check the SQL Server Logs for any out of the ordinary events ocurring around the same time as your initial slow login. Also, consider using SQL Profiler or Extended Events to capture login and logout events. The link below describes how to audit Failing logins, but you will want to expand on this to also include Successful logins as well.

    http://www.eraofdata.com/tag/login-failures/

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • It happens with both Windows and SQL Authentication so that rules out AD

    If happens with all servers and I have run profiler and verified that SQL doesn't know anything about the connection attempt until immediately before the connection is made.

    I thought about the "Check certification revocation" thing, but my PC has internet access and other people in the organization are not seeing similar issues. I'll try unchecking that, but I am not optimistic. Also if it were that issue, I would expect it to impact all connection types and not just database connections....

    I am really stumped on this one and am really hoping to avoid having to rebuild my machine...

  • This type of behaviour would be situation normal for a Java app, but not for a Windows executable. Using SysInternals Process Explorer, see what SSMS.EXE is doing during the several minutes of connection deplay. For example, it is chewing up a lot of CPU? Also, load Process Monitor, filter events by SSMS, and see what file activity is going on.

    https://technet.microsoft.com/en-us/sysinternals/bb795533

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply