1

I was out on a clients site today and installed some custom software that accesses a SQL server 2005 database that resides on a server on the clients site.

The software runs fine when ran directly on the server, the problem is when the software is ran on the client machines, anything that involves accessing the SQL database grinds the PC to a complete halt for several seconds. CPU usage on both client and server machines are low, memory usage on both looks to be fine.

Other functions on the client machines seem to be fast enough ie. internet, excel, word etc.

The custom software is installed in several other different client sites and SQL access speed has not been a problem so I am 99% sure its not software related. Both of the client machines and the server are all around 3 - 4 years old but have had a full format and reinstall within the last 6 months.

All 3 machines are pretty much identical hardware wise:
Pentium 4 2.66GHz
1GB RAM (2GB in server)
Windows XP (auto update is turned on so I presume patches are up to date)
All connected to a 100MB network

Does anyone have any idea what could be causing the client machine SQL access to be soo slow, or what tools (preferrably free) could I use to try and diagnose the problem?

3 Answers 3

1

Since you're saying that CPU and memory look good, I'd look at the wire and the queries the client is asking the server to perform.

On the client I'd be game to give Wireshark a shot and see what the conversation between the client and the server looks like (latency, retransmits, etc). I don't think I'd narrow my conversation to only the client and the server, though, during the first few captures on the client, just in case the client is emitting some other packets (spurious name resolution requests, attempts to connect to other services, etc) that might give you a clue.

On the server, an SQL Server Profiler trace can give you some pretty low-level information about what the client is asking for and how the server is responding. Microsoft provides a basic how-to document to get you started.

2
  • What Evan said. Start SQL Profiler on the server and first check whether the queries are running slow when called from client computers. Once that's eliminated, then you can see how much data is being pulled over the wire, and follow Evan's Wireshark advice.
    – user3914
    Aug 5, 2010 at 20:20
  • Thanks. I was able to run the profiler and SQL (as far as I could tell) was running fine. Wireshark looks like a great we program but looking at it on the clients site with him standing behind me for the first time was probably a bad idea, I really should have had a test run with it on a known good network :)
    – Zordey
    Aug 6, 2010 at 14:34
1

Could be that the clients attempt to connect first on a protocol that the server is not listening. For instance the clients attempt first net pipes over SMB and when that times out, they attempt TCP (this is the default behavior, btw). If the server is not listening on netpipes or the NetBIOS/SMB infrastructure at the client has issues, then the connections will appear to be extremely slow. When you connect on the server, it would connect directly with shared memory and succeed immediately.

See Configuring Client Network Protocols for how to (globally) change the network settings on the client machines. Or try using a connection string that enforces a specific protocol like datasource=tcp:hostname,port. Also, check the Server Network Configuration to see what protocols is the server actively listening.

1
  • Thanks, all this is totally over my head to be totally honest but I was able to check the server configuration against a known working version and they were configured the same.
    – Zordey
    Aug 6, 2010 at 14:50
1

Thanks to the posts above to help me narrow the problem down (i don't have the reputation to up-vote them :( )

After a lot of messing with settings etc. I, just out of curiosity, changed the connection string for SQL server on the client machines from referencing the server using its hostname to using its IP. This immediatly solved the problem and everything is running great.

I assume this is due to the fact the the 'server' is not actually running a server OS (its just another machine on the network running XP) so there is no proper DNS / WINS server to do the hostname translation??

Either way its working now, Thanks for the help.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .