SQL2005 Express 远程 VB6 应用程序速度缓慢
我有一个使用 MSDE 构建的旧版 VB6 应用程序。
随着许多客户的数据库增长到 MSDE 2 GB 限制，他们升级到 SQL 2005 Express。
直到今天，这已经被证明是非常成功的。
我花了一整天的时间对客户端网络进行故障排除，当通过“网络”连接 SQL 2005 Express 命名实例时，我们的应用程序运行速度慢得令人无法接受。
我说“网络”是因为它只有两台 XP SP2 机器 - 这里没有专用服务器。 没有广告。
为了解决这个问题，我在两台机器上安装了 SQL 2005 Express，并在两台机器上放置了数据库的副本。 我什至使用我们现在拥有的 SQL2005 Express 安装例程完全重新安装了我们的应用程序。 无论我恢复旧的 MSDE 数据库还是使用新创建的 SQL 2005 Express 数据库，都没有什么区别。
当运行我们的应用程序并连接到任一计算机的本地服务器时，性能都很好。 一旦您将任意一台 PC 上的应用程序连接到另一台 PC 上的服务器，速度就会慢得无法工作。 （与组合无关）。
现在，我已经重建了统计数据（exec sp_updatestats），重建了所有索引，禁用（暂时）防火墙和病毒软件，并抓住了无数其他救命稻草。
我在两台机器上都运行了 FileMon 和 ProcessMon，甚至编写了一个小测试应用程序来简单地连接和查询数据库中的表。 它运行速度也很慢 - （大约需要 5 - 6 秒才能连接）。
当 SQL Server 写入日志文件 (c:\program files\microsoft sql server\mssql.1\log files\log_12.trc) 时，监视器（文件和进程）显示延迟。
不过，其他工具，如 SQL Management Studio Express 甚至 SSEUtil（我发现的 SQL Server Express 诊断实用程序）在从客户端连接到服务器时运行得很好。 查询（即使是大型查询）按照您的预期运行。
我确信这个问题是环境问题，因为我们有很多站点运行看似相同的设置，但没有此类问题。
有人可以告诉我应该做什么来隔离这个问题，甚至提供任何可以帮助解决这个问题的线索或建议吗？
I have a legacy VB6 application that was built using MSDE.
As many client's database grow towards the MSDE 2 GB limit they are upgraded to SQL 2005 Express.
This has proven very successful until today.
I have spent the entire day troubleshooting a client's network on which our application runs unacceptably slowly, when connecting the a SQL 2005 Express named instance across the "network".
I say "network" because it is only two XP SP2 machines - there is no dedicated server here. No AD.
In trying to isolate this problem I have installed SQL 2005 Express on both machines and placed copies of our database on both machines. I have even completely reinstalled our application using the SQL2005 Express install routine we now have. It makes no difference whether I restore an old MSDE database or use a newly created SQL 2005 Express one.
When running our application and connecting to either machine's local server performance is fine. Once you connect our application on either PC to the server on the other PC, it is unworkably slow. (Regardless of the combination).
Now, I have rebuilt statistics (exec sp_updatestats), rebuilt ALL indexes, disabled (temporarily) firewalls and virus software and clutched and countless other straws.
I have resorted to running FileMon and ProcessMon on both machines and have even written a little test application to simply connect and query a table in the database. It too runs slowly - (takes about 5 - 6 seconds to connect).
The monitors (File and Process) show delays when SQL Server is writing to a log file (c:\program files\microsoft sql server\mssql.1\log files\log_12.trc).
Other tools though, like SQL Management Studio Express and even SSEUtil (a SQL Server Express Diagnostic Utility I found) run perfectly when connecting from the client to the server. Queries (even large ones) run as you would expect.
I feel sure this problem is environmental as we have so many sites running what would appear to be the same setup, with no such problems.
Can someone tell me what I should be doing to isolate this problem or even offer any clues or suggestions that could help solve this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
制作一个清单并系统地完成它：
在这里添加所有帖子的所有建议以及我在下面添加的一些建议：
等，等等，
Make a checklist and systematiccaly work through it:
Add all suggestions of all posts here and some I add below:
etc. etc,
这可能是由于缓存的查询计划不代表数据，即使您已经重建了索引并刷新了统计信息。 您描述的症状（即查询在 SSMS 中运行良好，但在应用程序中运行不佳）通常是由错误缓存的查询计划引起的。 SSMS 在幕后发出“WITH RECOMPILE”。 如果您正在调用存储过程，请暂时将“WITH RECOMPILE”添加到其定义中并检查结果。
This might be due to a cached query plan which is not representative of the data, even thought you have rebuilt indexes and refreshed statistics. The symptom you describe (namely that a query runs fine from SSMS but not from an application) is often caused by a wrongly cached query plan. SSMS emits a "WITH RECOMPILE" under the covers. If you are calling a stored procedure, temporarily add 'WITH RECOMPILE' to its definition and check the results.
您是否尝试过从另一台计算机连接到“服务器”PC？ 会发生什么？
您是否尝试过将“客户端”连接到另一台“服务器”计算机？ 会发生什么？
问题可能只是一些普通的问题，例如不稳定的网卡或电缆。
在你进一步绞尽脑汁之前可能值得检查一下......
Have you tried connecting to the "server" PC from another machine? What happens?
Have you tried the "client" to another "server" machine? What happens?
The problem could just be something as mundane as a flakey network card or cable.
Probably worth checking before you beat your brains out any further...
您在“表面配置”工具中启用了哪些网络协议？ 您可以更改连接字符串以使用（临时）硬编码 IP 地址吗？
What networking protocols have you got enabled in the 'surface configuration' tool? Can you alter your connection strings to use (temporary) hardcoded ip addresses?