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?
Best Answer
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.