SQL2005 Express slow from remote VB6 application

Question

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?

Answer 1

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.

Answer 2

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...

Answer 3

Make a checklist and systematiccaly work through it:

Add all suggestions of all posts here and some I add below:

1. Network Cables
2. Network Speed
3. Defrag Hard Disk
4. No Network errors - do a ping and look for missing packets
5. Ram per Machine
6. Processors
7. Virusses

etc. etc,

Answer 4

What networking protocols have you got enabled in the 'surface configuration' tool? Can you alter your connection strings to use (temporary) hardcoded ip addresses?

Answer 5

I have the same problem but only if the ado.provider is not set to the SQLNative one.