SSMS: First connection to DBMS takes over 15 minutes

Question

Post reply

SSMS: First connection to DBMS takes over 15 minutes

farside41

SSCrazy

Points: 2083
More actions
January 8, 2016 at 8:22 am

#307580

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.

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

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

cmc123 Right there with Babe Points: 762 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

farside41 SSCrazy Points: 2083 More actions · Answer 3

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.

farside41 SSCrazy Points: 2083 More actions · Answer 4

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

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

Lowell SSC Guru Points: 323518 More actions · Answer 6

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!

farside41 SSCrazy Points: 2083 More actions · Answer 7

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

farside41 SSCrazy Points: 2083 More actions · Answer 8

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

cmc123 Right there with Babe Points: 762 More actions · Answer 9

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.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

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

Lowell SSC Guru Points: 323518 More actions · Answer 11

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!

Eric M Russell SSC Guru Points: 125561 More actions · Answer 12

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

farside41 SSCrazy Points: 2083 More actions · Answer 13

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...

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

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