December 29, 2011 at 8:23 am
I've been having a problem with one of our SQL Server installations. When I make a SQL connection to the instance, it is very slow to connect. Everything I do on the server is likewise very slow. When I try to create a new login, and I click on "User Mapping" it actually becomes unresponsive. After ten minutes of non-responsiveness, I closed my SSMS.
The weird thing is, this only happens to me. The other DBAs in my group do not experience any of these symptoms. Also, none of my other desktop applications suffer from any slowness at all.
I have cold-booted my workstation twice, and it hasn't helped.
Has anyone else ever experienced anything like this, and do you know what the cause is and how to fix it?
Thanks.
December 29, 2011 at 8:36 am
Did you try RDP into the server and trying to connect? If that is working like it should then you can definitely conclude that it is not your Server installation or your User. It could just be your local policies or firewall that is causing this.
-Roy
December 29, 2011 at 8:48 am
Roy,
I have no problems at all RDP'ing to the server. It's only the SQL Server connection that is slow and unresponsive.
Also, I have no problems connecting to any other installation of SQL Server on any other box. It's only this one installation that I have problems with, and it is only me who is having any problems on that box.
Do you know which local policies or firewall settings might be the culprit? And, are you talking local policies on my workstation or on the server?
Thanks.
December 29, 2011 at 8:56 am
Local policies on your work station. Firewall on your work station. Those are the two things that stand out that could cause issues like this.
Regarding RDP, I was asking if you log on to the server using RDP and in that RDP session if you try to connect to DB, is it still slow?
-Roy
December 29, 2011 at 8:56 am
I recently experienced something similar. Two developers on our team could hardly get any work done in SSMS due to the horrible sluggishness in the application. No one else experienced any problems.
The problem is the client. In our case, the problem traced back to network latency. SSMS connects to the internet to validate signed certificates on managed code, and it scans networked file shares as a part of its normal operations.
Here is a little document of fixes that I put together. Perhaps it will help you. 🙂
/********************************************************************/
The problem generally falls into one of the following categories:
1. The internet is not responding in a timely fashion
2. A network share is not responding in a timely fashion
Internet Latency
SQL Server Management Studio contains a lot of managed code from Microsoft, which is signed when they ship it. At startup, the .NET run time tries to contact crl.Microsoft.com to ensure that the certificate is valid. (There were some fake certificates issued in Microsoft’s name a few years ago, so this is a valid security issue.) If there is no internet connection or the corporate firewall screens out these internet requests or there is some other problem contacting crl.Microsoft.com, then SSMS will hang until the certificate check times out. (This issue actually applies to all managed code, for example: Visual Studio.)
Try the following steps.
1. Add an entry to your local HOSTS file which points crl.microsoft.com to 127.0.0.1
a. Exit SSMS and Visual Studio
b. Press the keys [Win]+[R]
c. Type “Notepad %SystemRoot%\system32\drivers\etc\hosts” and press Enter
d. Append the following line to the end of the file: 127.0.0.1 crl.Microsoft.com
e. Save the file
f. Restart your applications
2. If that doesn’t help, you might need to disable the “Check for publisher’s certificate revocation” option in Internet Explorer
a. Exit SSMS and Visual Studio
b. Open Internet Explorer
c. Select Tools --> Internet Options --> Advanced
d. Scroll down to the Security section
e. Uncheck “Check for publisher’s certificate revocation” option
f. Click OK
g. Close Internet Explorer
h. Restart your Applications
Internet latency can also delay startup if SQL Server Management Studio is configured to access online help resources in the background. (Unfortunately, this is the default setting.) This can be changed as follows:
a. In SQL Server Management Studio, Select Tools --> Options --> Help --> Online
b. Check “Try Local First, Not Online”
c. Press OK
d. Restart SSMS
e. The steps are exactly the same for Visual Studio
Your computer might also be configured to send error reporting data to Microsoft. This is set during installation, but can be changed afterwards as follows:
a. Exit SSMS and Visual Studio
b. Select Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Error and Usage Reporting
c. Uncheck both boxes (for what we’re paying Microsoft can collect their own error data).
d. Click OK
e. Restart your applications
Network Latency
If your windows default user directory is on a network share, then network latency can cause a couple of related problems. First of all, SQL Server Management Studio – by default – will try to scan your default user directory whenever you try to open or save a file. If that network share is not responding, then SSMS will simply hang until the connection times out. Similarly, if SQL Server Management Studio is configured to auto-save your work (and again, this is the default setting) it is almost certainly trying to save to your default user directory.
1. SSMS (unlike Visual Studio) does not have a way to change the default file locations through the user interface. So, you have to fix this in the registry.
a. Update all file locations that reference a network share in the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell
b. Also check for entries in this key: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\ProjectLocationEntries
c. The above keys are for SSMS 2005. The equivalent keys for SSMS 2008 are located here: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\...
2. You have two alternatives to fix latency resulting from the auto-save feature. First, you can disable the auto-save feature. This again requires you to edit the registry.
a. For SSMS 2005, open the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover.
b. The equivalent key for SSMS 2008 is here: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover.
c. Set the value, “AutoRecover Enabled” to zero.
3. The other alternative is to change the location of the TEMP and TMP directories where windows saves temporary files.
a. Right-click on Computer and select Properties
b. Select Advanced System Settings
c. Click the Advanced tab
d. Select Environment Variables
e. In the top window, User Variables, update the TEMP and TMP variables to point to a local drive
/********************************************************************************/
I hope this helps fix your problem! 🙂
December 29, 2011 at 9:29 am
Roy,
RDP'ing and opening SQL Server directly on the box works fine. No slowness or unresponsiveness.
Since my last post I have also discovered that at least one other person in the same row of cubicles that I am in is having the same problems. People on the other side of the aisle are not experiencing any problems.
It's starting to look like a networking issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply