Solved

Improving SQL Server 2005 Express performance

Posted on 2009-05-04
6
1,021 Views
Last Modified: 2012-05-06
Hey folks,

I'm a MySQL/PHP guy mostly, so this area is a bit new to me. We have a shipping application, (ShipWorks by Interapptive) that utilizes an SQL Server database. We're running it on SQL Server 2005 Express right now, which worked fine in a local environment, but we've moved it to a remote/web server so we folks at home can connect to it and see order information, etc.  

Problem is, when we moved it to the remote server, performance plummeted. The web machine itself is pretty beefy, quad core E5310 @ 1.60 ghz, 4gb ram, Windows server 2k3 enterprise, etc.  Well over 3gb ram free at any one given time, and CPU usage <10% on average.

As a test, even running a simple select query takes on average 400ms, whereas in local environment it's <50ms.  

As a side note, I also have MySQL on the same machine, and performance is extremely fast there, so I'm thinking configuration related perhaps. It's a base install of SQL Server 2005 Express, nothing changed.

Some other thoughts:
Would upgrading to 2008 help?
Would utilizing a paid license help? e.g., are there features of the paid licensed version that would enhance performance greatly compared to the free one, etc.


Thanks!
0
Comment
Question by:jmoriarty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:robdcoy
ID: 24301445
I have a couple of questions.

1.  Are your Database and Log files on the same drive?  Moving them off of the System drive, and then putting the DB and Log files on different physical drives would help.  I do this with SQL Standard, but I don't know if it is possible with Express.

2.  4GB of RAM on the other machine?  Is it Windows 2003 Ent 64bit?  If it is only x86, you aren't using past 3GB.

3.  Set the system to give background services higher priority under System Properties.  Sometimes this helps (I've heard, but personally, I can't tell a difference).

4.  Have you explorered network teaming?  I have SQL Servers with 4 NIC's for performance.

5.  SP3 Installed for SQL?

6.  More of a statement here, but SQL Server 2005 does handle more operations per second than Express.

One thing to keep in mind is that when SQL was local, it didn't have far to go to request data.  I would look mostly at the network to increase the performance.  I can give you some options upon your reply.  Hope this helps.  :)

Rob
0
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 250 total points
ID: 24301453
FYI only, SQL Express (2005 & 2008) limit itself to 1 gig of memory and 1 cpu - so your quad core 4GB is going to waste on SQL Express :)  That said, I'm letting you know more as an FYI than an answer since the addition 350ms probably isn't due to this limitation since it runs fine locally.
0
 
LVL 3

Assisted Solution

by:robdcoy
robdcoy earned 250 total points
ID: 24301478
I agree with usachrisk, but having said that, having more memory for other Windoze services helps SQL performance.  ;)  I didn't know there was a 1GB limit with Express!  Thanks for the info usachrisk.  I learn something new everyday.

PS:  I have to use SQL 2005 Express for my access control system that is getting installed tomorrow.  I better get smart soon!  Sorry for the departure of subject.  lol
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24301786
apart from the above suggestions of the experts, INDEX is the best thing which can BOOST up your performance, have you consider this fact and used it?
0
 

Author Comment

by:jmoriarty
ID: 24302333
Hi RiteshShah,

If you mean table indexes, then yes, there are some. I have limited capabilities to modify the schema since it's a third party license though.

Usachrisk,

Thanks for the note about memory usage - the server process is taking less than 200mb on average though. The fact that it's only able to use 1 core actually may partially explain the issue; there are other items running on the web server, such as mysql, and some low end php content, etc, but even with that, overall cpu usage averages 5-8% at best.


Robdcoy,

Thanks for the detailed response - here's my replies to your questions.


1.  Are your Database and Log files on the same drive?  Moving them off of the System drive, and then putting the DB and Log files on different physical drives would help.  I do this with SQL Standard, but I don't know if it is possible with Express.

-I'm going to assume they're on the same drive, since it's a basic install with no modifications as of yet, but that's definitely something I can look into/adjust.

2.  4GB of RAM on the other machine?  Is it Windows 2003 Ent 64bit?  If it is only x86, you aren't using past 3GB.

Yeah, it's 64bit version. It's a leased dedicated web server basically. Our local machine was far less beefy. (but it also wasn't running any other applications, either)

3.  Set the system to give background services higher priority under System Properties.  Sometimes this helps (I've heard, but personally, I can't tell a difference).

-already done

4.  Have you explorered network teaming?  I have SQL Servers with 4 NIC's for performance.

I haven't as of yet, but that's a good idea. There's 2 NICs in it at the moment, 1 primary and a backup incase the primary gets overloaded.

5.  SP3 Installed for SQL?

Yes


0
 

Author Comment

by:jmoriarty
ID: 24303421
Okay, moving log file to a different HDD didn't help any.  I'm almost thinking network related, but we're only 1 state away from the data center (about 4 hours or so) on a 100mb pipe with about 3% usage at most.  But that's the only thing that makes sense at this point, given my limited knowledge of SQL Server.

A simple query of:

select * from customers where customerid = 1

took 500ms to execute on the remote, whereas the same on local took 203ms


It'd be different if we were pushing massive users/data, but the database is only about 400 orders strong, with 1-2 users connected via the shipping application. And right now, I'm the only one connected to it period when running these queries, given that its 7:20 AM.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question