Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Improving SQL Server 2005 Express performance

Posted on 2009-05-04
6
Medium Priority
?
1,024 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 1000 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 1000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

721 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