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.