SSMS Remote connection very slow (SQL Server 2008 R2 64bit)

I am experiencing very slow response (queries and general operations) from SSMS running on my development pc when connection to a NEW remote production SQL Server. The initial connection load speed is ok.  I frequently see "not responding" in the banner.

I have had no performance issues when remotely connecting to existing production SQL servers running the same databases.
When I run SMSS directly in an RDP session on the new production SQL server it is very fast.

On my Develoment PC where I am performing the remote connection to the server I am running Win 7 Pro 64bit and Microsoft SQL Server 2008 R2 SP1 Web Edition. 
On my old servers, where remote SSMS performance is good I am also running Microsoft SQL Server 2008 R2 SP1 Web Edition 64
On my new server, where remote SSMS performance is horrendous, I am Microsoft SQL Server 2008 R2 SP1 Enterprise Edition 64

Another Development PC is experiencing exactly the same behavior and it is running SQL Server 2008 R2 SP1 Enterprise Edition so I think that may rule out that the SQL Edition difference on the client as being the culprit.

The connection strings all use server-ipaddress and port 2433,  Protocol setting on the old and new server appear to be the same.
I have a fast broadband connection. A ping to the Remote SQL Server is fast and the same for old and new servers.

New SQL Server installation has:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
Microsoft SQL Server 2008 R2 Setup Discovery Report 
Product Instance Instance ID Feature Language Edition Version Clustered 

===================================================================================

Microsoft SQL Server 2008 R2 MSSQLSERVER MSSQL10_50.MSSQLSERVER Database Engine Services 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2 MSSQLSERVER MSSQL10_50.MSSQLSERVER SQL Server Replication 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2 MSSQLSERVER MSSQL10_50.MSSQLSERVER Full-Text Search 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2   Management Tools - Basic 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2   Management Tools - Complete 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2   Client Tools Connectivity 1033 Enterprise Edition 10.50.1600.1 No 
Microsoft SQL Server 2008 R2   Integration Services 1033 Enterprise Edition 10.50.1600.1 

=========================================
My Windows 7 Development PC (Win 7 Pro 64bit):
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Web Edition 
(64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Microsoft SQL Server 2008 R2 SP1 Setup Discovery Report 
Product Instance Instance ID Feature Language Edition Version Clustered 

=============================================================

Sql Server 2005 SQLEXPRESS MSSQL.1 Database Engine Services 1033 Express Edition 9.4.5000 No 
Sql Server 2005 SQLEXPRESS MSSQL.1 SharedTools 1033 Express Edition 9.4.5000 No 
Sql Server 2008 MSSQLSERVER MSSQL10_ 50.MSSQLSERVER Database Engine Services 1033 Web Edition 10.51.2550.0 No 
Sql Server 2008 MSSQLSERVER MSSQL10_50.MSSQLSERVER Full-Text Search 1033 Web Edition 10.51.2500.0 No 
Sql Server 2008 MSSQLSERVER MSRS10_50.MSSQLSERVER Reporting Services 1033 Web Edition 10.51.2550.0 No 
Sql Server 2008   Management Tools - Basic 1033 Web Edition 10.51.2550.0 No 
Sql Server 2008   Management Tools - Complete 1033 Web Edition 10.51.2500.0 No 
Sql Server 2008   Client Tools Connectivity 1033 Web Edition 10.51.2500.0 No 
Sql Server 2008   Client Tools Backwards Compatibility 1033 Web Edition 10.51.2500.0 No 
Sql Server 2008   Client Tools SDK 1033 Web Edition 10.51.2500.0 No 


=========================================================================

As far as I can tell the server configuration on the old and new servers (other than SQL Edition) are the same.
Remote SSMS connections are using IP and Port 2433 on both the old and new servers and SQL Account Authentication

I tried the following without any noticeable performance improvements:

  • temporarily turning off the Firewall on the new SQL Server - no change
  • turning off intellisense on dev sql - no change
  • set compatibility mode to Windows XP SP3 on the SSMS icon - no change
  • there no biometric services are running

Any advice or suggestions are much appreciated as remote SSMS is an operational requirement and it is currently unusable.

Thanks, ARF_21

January 9th, 2013 11:26am

Hello,

Are you sure that a Windows 7 Pro can support a SQL Server Entreprise Edition ? ( Developper yes , but Entreprise ? )

What is the authentification you are using to connect ?

What is the memory installed on your new server ? What is the memory dedicated to your SQL Server Entreprise ? ( an Entreprise edition is more greedy than a Web edition : 4 GB is a minimum for Entreprise , for Web : it is 4 GB maximum )

See http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx#EEx64

Please, could you explain what you mean by "Not responding" ? Is it for queries ? Have you tried the same queries on your new and old SQL Server ?

I have done tests between 2 computers ( in a workgroup ==> SQL Server authentification ) from SSMS 2012 and SQL Server 2008 R2 Express SP1, no real problem except when i am trying to get more 5000 records from the SQL Server 2008 R2 ( with 4 GB only for the memory )

Have you checked wether the computer on which the new Entreprise SQL Server is installed has network problems ?

I have not understood

Microsoft SQL Server 2008 R2 SP1 Setup Discovery Report
Product Instance
Instance ID Feature Language Edition Version Clustered

Why i have seen" Version Clustered ?

It is possible that a moderator moves your thread towards the Setup and Upgrade forum ( no thread to recreate, no lost post, no thread dying in a bad forum, for myself , i prefer to wait for the agreement of the original poster )

We are waiting for your feedback to try to help you more efficiently

Have a nice day

Free Windows Admin Tool Kit Click here and download it now
January 10th, 2013 1:33am

RESOLVED:  After many hours of troubleshooting and trying different protocol and firewall settings, I resolved this issue by placing an entry in my local /client hosts file that maps the target SQL Server name to the Server's public IP address that I am using in the connection string.

I am not exactly sure why this fixed it or if it is an indication that some setting is missing on the target SQL Server, but right now my Remote SSMS is like greased lightning and I am a happy camper.

ARF_21

January 11th, 2013 11:25am

It appears a look-up issue.....register the servers via FQDN.
  • Proposed as answer by Jason Tozer Monday, February 11, 2013 8:16 PM
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2013 11:16pm

Hi,

Can you tell me the syntax you used to   "placing an entry in my local /client hosts file that maps the target SQL Server name to the Server's public IP address that I am using in the connection string".

Thanks,

ALex

October 23rd, 2013 9:40pm

Hi Alex,

The short answer is start, run, notepad c:\windows\system32\drivers\etc\hosts

In the host file it is ipaddress <press tab> type in name of pc, save the file, it may be marked as read only, eg 111.222.333.444 Myhostname

Free Windows Admin Tool Kit Click here and download it now
November 1st, 2013 1:23am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics