Solved

Improving SQL Server 2005 Express performance

Posted on 2009-05-04
6
1,009 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
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now