Discussion Locked

SQL Slow down with workstations

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Here is the situation to the best of my Knowledge. We have a 2003 Server running SQL 2000 SP4. This houses our data base. When Running our application locally on this server every query runs fast and fine. When you run the same query on a workstation that is running off the SQL Server it is significantly slowing down the query process. Any ideas would be very helpful.

6 replies

by cg it

The server isn't powerful enough.

Probably need more memory, SCSI drives instead of SATA, dual processor depends on how many are accessing.

The other factor is the network itself and for that you'll have to do some network testing to find out where the bottlenecks are.

by tony hopkinson

ie one instance of the client running on teh server or a workstation, then there are only a few reasons.
Workstation is underpowered and you are doing a lot of stuff clientside

Network bottleneck and you are passing a lot of data back and forth.

The server has enough spare muscle to cope with a client intensive design.

If it is network you are going to get hammered for query's with large result sets in width and or length.

Might be an idea to install sql tools on the workstation and then run some example queries from the app on both machines.

If you are seeing serious differences that would point to a network problem of some description, if not, then it's the app itself.
NB even if there are substantial performance differences between the workstation and the server this still suggests to me a client intensive design.

One of the classics you often see is people doing things like select * from mybiggesttable and then using ado's locate (which works clientside !) I recently ported a desktop backend which was properly optimised to work like this. In client server though it was horrible. 3.5 seconds for the locate, 0.1 when I shifted to select * from mybiggesttable where ID = ???

HTHs

by Member 22070467

What network protocols are you using? When you connect to a local SQL machine you are almost certainly using Shared Memory(which is very fast, but can only be used locally). When you connect through the network you are likely using TCP/IP or named pipes, so check your protocols. Likely though, it is the network. What are the specs on the server you are using?

Tim

by uberg33k50

By what you describe the network performance sounds like a likely culprit. If the network tests out OK, though, check with your software vendor and see if they have any kown issues with the antivirus software you are running. I have an application that uses a SQL database and they do not work well with Symantec or McAfee only Trend Micro.

by Member 22070467

Thats true. In fact, on clustered sql instances, the use of antivirus software is strongly discouraged by Microsoft.

by support

We are also experiencing a slowdown of our SQL application - it slows down as the day progresses. Running switched 100MB ethernet with TCP/IP on duap Xenon processor with 4 GB of ram and mirrored Ultra320 SCSI drives.

This discussion is closed to new replies.