• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Sybase iAnywhere SQL database performance issues

I have a client who I manage the server for.  A company has installed a Sybase iAnywhere database for them that tracks inventory.  The software front end is used to enter transactions, etc.  The problem I am having (as they have laid this at my doorstep) is that the system is slow according to the vendor.  If I run a query in the Advatage Data Architect on the terminal server, it returns in between 7 and 60 seconds.  If I run the same query on the DB server itself, it takes 16 ms - MUCH faster obviously.  I would imagine the terminal server query would be a little slower due to network traffic, but I don't think it should be that slow.  

The DB server is Windows 2003 SBS with 8 GB of ram.  It has MS SQL 2000 and Exchange 2003 on it running.  I also have Trend Micro installed on that server.  This server has two dual-core processors.

The terminal server is Windows 2008 with 4 GB of ram and one dual-core processor.  They have maybe 8 users on their server at most at any one time.

Task manager doesn't show either box being hit hard when I do any queries.  Memory, processory utilization and network traffic are all low.

Any ideas about where I can look?
0
pmasseycpa
Asked:
pmasseycpa
1 Solution
 
grant300Commented:
One experiment that you should do immediately is to install the client on a stand alone desktop and see what kind of response times you get.  They will depend on how much data you are moving, how fast the network is, and what kind of network latencies you have.

BTW, when you issue the query, how many rows are you returning?  Is it the same problem for a simple query like "select @@version"?

Another test to run is to do a ping from the TS machine to the database server and see what kind of round trip delays you are getting.  Do it the opposite direction as well, from the db server to the TS machine.  Obviously, if you have network problems at that level, you can stop looking and start browbeating your net admin(s).

Networks will always add latencies to database operations but is sounds as if yours is extreme.  Another possibility is that it might have something to do with the way Advantage is interacting with the Terminal Server and it's latencies.  For instance, if Advantage paints the rows as they come back and Terminal server is updating the remote screen, there could be a string of cascading latencies that really add up.

One thing to have your net admin look at is turning off TCPWAIT.  TCPWAIT is an option on the network stack that tells the TCP level to wait up to a second before sending the physical packet out.  It does this so that if there is more data headed to the same destination, it can gang the packets together and, theoretically, reduce the overall traffic and overhead.  Most of the time, you want to turn TCPWAIT OFF but for some reason, various platforms turn it ON by default.

Let us know what you find.

Regards,
Bill

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now