*SQL Prompt performance over VPNs and high latency networks*

Bart Read

We have been able to identify an issue that can cause SQL Server Management Studio to perform slowly/become unresponsive whilst typing if you are editing a script that is present on a mapped network drive or remote machine, with SQL Prompt enabled. This is most pronounced when operating over a VPN or other high latency network, but could cause problems on any network under moderate load when editing files stored on another machine.

It is likely, although we have not yet confirmed this, that Visual Studio 2005 and 2008 are also affected by this issue since SSMS is based on the Visual Studio Shell. Also, we have not confirmed whether SQL Server 2000 Query Analyzer is affected by the same problem.

Up until now we have received sporadic reports of this issue but have been unable to reproduce it until we were kindly given access to a customer's laptop and VPN at Tech Ed 2008.

The unresponsiveness occurs because of a side-effect of retrieving text from SSMS: on every keystroke SSMS appears to check whether the file being edited has been modified. It may even do this more than once since we have found that several network roundtrips are happening. Unfortunately these are done in the foreground thread, which blocks until each round-trip completes, and thus results in a noticeable delay between keystrokes being registered in the editor. In extreme cases this delay can be up to several seconds in duration.

For now we have no fix, but it is possible to work around the problem: rather than working directly with remote script files, copy them to your local machine, and edit them locally, then copy them back when you're finished. Likewise, if you use a SCM tool, make sure your working directory is on your local machine rather than a mapped network drive or UNC path to a remote directory.

We are investigating the best way to address this problem more satisfactorily in a future version of SQL Prompt.


Many thanks,
Bart