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