Solved SQL Server 2000 Performance

[Resolved]SQL Server 2000 Performance

My company is a software company. We lease servers to host our clients SQL databases. One of my larger clients with approx 35 users is having performance issues. The software is installed on all machines and we just point it to the IP where the db is and they connect via TCP/IP.

The current server is:
Server 2003
P4 3.2 GHz cpu
1 gig of ram (don’t laugh "“ long story)
HUGE amount of bandwidth at the hosting site.

One particular task we’re using as a test task "“
It takes 60 seconds to complete when ran off the server. It was a little faster early in the morning with only a couple of users connected.
Same task when ran locally on my machine with their backup takes 10 seconds.
I know it’ll never get down to 10 seconds, but it’d be nice to get it faster then the 60 seconds.

We can get a newer machine-> 2.8 GHz Quad Core XEON with 4 gigs of ram for not too much more money.

To me upgrading seems like a blatantly obvious thing to do…But my main developer that’s helping with this says it isn’t that simple?? Task manager shows some available ram "“ the pagefile and the sql service aren’t too huge in size.

The SQL .mdf file in question is 1.5 gigs in size…(the .ldf file is real small)

My clients main off is getting:
6500 kbps download speeds
1400 kbps is what my office is. (sad to be me)
So I’m guessing that while bandwidth is important "“ it’s not an issue in this situation.

I’d appreciate any feedback or suggestions.

 

Guess people fell asleep reading the above...

My developer guy came to the comclusion that all is well except the hardware. We're having 2 more gigs of ram added tonight. Will be interesting to see how it behaves.

 

I'm not so sure that more RAM will do the trick. I have experience w/ mySQL but not w/ MS SQL. But both are based on the same SQL basic functions.

The usual cause of sluggish performance in SQL servers is unoptimized tables and code errors in queries (read non-OO code or laziness).

Thirty five users is really not that many at all (compare to this bbs which may have hundreds of queries taking place at the same time).

A simple failure to use a ssqlclose() function at the end of a query will cause the database connection to remain open, using memory. In this case, memory does not refer to just RAM because Windows is constantly paging between RAM and the swap file.

Also, in the above, TCP connections may remain open and the server could be slowed waiting for connections to close completely.

Don't overlook the fact that programmers can err here & there.

 

Thanks for the reply Tony. And I understand what you’re talking about.

The software is nine years old and I’ve been supporting it for seven of those years. The same programmer that’s been helping me is the same guy that started the build way back when.

The software is a full blown accounting package that has been tweaked/upgraded many times over the years. The area of the software we’re using for testing is known to be a high resource hog. The area is cutting accounts payable checks.

As I mentioned, I’m using their exact database, this task takes me 10 seconds to perform locally and it takes 60 seconds to do it when I’m connected to the server. We feel confidant that if it were a stored procedure problem that I too would experience a delay.

 

Thanks and understood.
The machine in question does nothing but host the database.

The gigabyte card issue has come up...Doing a speed test on the server shows 35,000kbps

 

The software was developed to be used w/ the version of SQL at that time. SQL has changed in 9 years. Some functions no longer exist and new ones had to be implemented to align w/ those changes. And new functions have been added to SQL to increase query efficiency.

Also, there are multiple methods of doing complex queries. Some are more efficient than others. For example, using sessions/cookies will increase efficiency, the session temp dir or cookies can store global variables which can be eliminated from queries in the code. Example vars are usernames, md5 passwords, account type, etc..

And javascript can be used to pass certain processes off to the user's comp rather than the server. Anything that reduces server load w/out compromising security is worthwhile using.

Also, I assume SSL (HTTPS) is being used as it's an accounting application. Or maybe a VPN connection. Either can potentially slow things down a bit unless configured most efficiently.

 

The main programmer is one of those 100grand a year kind of guys that is probably underpaid. He's 15 to 20 years younger then me and falls into the super geek catagory...

The software runs well on SQL2005....AND, we had an outside company audit our software due to possibly selling the source code to a company in India. Here's a blurb from the auditing company:

....They are looking at buying the source code due to wanting to change the currency....

Oh and adding the ram didn't seem to make any difference.

To make things more complicated..I connected to three different servers. Two of them we manage and one is managed by the client.

One (that we manage) of the three seems to have the same performance slowness...Go Figure.

 

The guys found out it is an issue with the code in our newest version....The area is Budget Entry...

Clicking the OK button to finish the task updates a boatload of records. In the clients case, it was a little over 5000 records. A related glitch is that it does the update even if nothing was changed. (note to self - don't use the OK button to get out of the screen)

 

Glad the cause was spotted. Saved some $ on unneeded system upgrades. Another good indicator is that you have a coder who is willing to look for his errors...keep him!

 

Bilbus - funny you should mention the things you mentioned...One of our five leased severs went down Saturday morning at 4am...Luckily I was already awake and caffinated.

The hosting company we use is pretty heavy duty. They host thousands of computers...Its one of those fancy joints where the air ducts are under the floor so that it flows past sensors in case of fire etc...They received a notice that the machine was down...They then email and called me...In short, the machine only needed a reboot which they or I can do from the control center portal.

My company doesn't exactly have a plan "B" - shame on us...The machine are only used to host SQL databases that get backed up each night and we send a copy of the backup to a server in my office. So if if a machine were to die, we'd get a different one - install SQL and restore the db...

 

This DC has a bunch over everything. I'd guess the majority are rack mount...I haven't been there in a few years..Some of the private cages are pretty cool looking.

The last server we got this past summer was a PowerEdge 840.