I've recently transferred a site to a dedicated host, and because it was using SQL server installed a copy of SQL Server Express 2005 on the server. However, running scripts have become a lot slower, and I'm trying to get to the bottom of why.
The script is as follows:
select * from tableA
inner join tableB on tableA.field = tableB.field
inner join tableC on tableA.field2 = tableC.field2
On the webserver, this script times in at 35 seconds, on my local machine (running developer 2005) it times in at 2 seconds. Thinking it may be traffic related, I took the site offline, waited a minute or so and tried again with the online server -- no difference, again it timed in at 35 seconds. So it isn't traffic related, and it isn't remote distance related (the shared server and database in the building that we _used_ to be on timed in as quick as the offline database). I have come to the conclusion that it may be specific setup related, or it could simply be the database engine used by Express is far slower.
Does anyone know which of these it is likely to be, and if the latter is the case how much quicker I could expect SQL Server Standard would be ?
Thanks in advance,
- Graham
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I've updated statistics, index fragmentation is very low (< 5% across pretty much every table), backed up the database and restored the db offline.
No difference in script running time (ok, 33 seconds online and still 2 seconds offline).
Memory allocation I will have to do tomorrow when I can get back to work, but I _suspect_ it will be left unlimited (it's the only real resource requirer on the server). I will let you know if this turns out not to be the case.
I'm aware Developer behaves like Enterprise, and that was my initial reason for suspecting it to be the db engine.
Bhess1:
Yes, server is dedicated, no other activity on the server.
Server's specs are:
Single CPU (3 GHz Pentium 4)
1 GB RAM (PC2700)
160 GB SATA HDD
Running Windows Server 2003
Laptop running offline has:
Single CPU (Athlon 64 3000)
1 GB RAM (PC3200)
80 GB Ultra-ATA HDD
Running Windows XP Pro SP2
So from what I see there should be little, if any, difference from a specs point of view.
Remote box files have very low fragmentation, similarly to local.
1. Physical disk fragmentation is very low
2. Don't have access to do this from home -- what is most likely to cause in IO bottleneck on the server when all users are disconnected (apart from me) that is different to the laptop setup?
3. Yes, I agree, but this is exactly the same as the local laptop, which is also a single harddrive single processor machine - Is this a SQL Express specific problem?
Because it is express, there are no backups automated, and I am the only one attached to the db when I take others offline. I even tried detaching and reattaching the database and running again, and it made no difference to the running time. I suspect it is unlikely to be a scheduled task type of activity at has been running slow constantly since we transferred (about 2 and a half weeks ago).
No AV tasks running. With a bit of playing around I've sorted my PW to get access to the server, disk, processor and memory are all virtually nil 95% of the time (disk under 20%, Memory and CPU under 10%).
Have we scrapped the idea of the Express db engine being the problem or are we eliminating other options first?
And is there any setup options other than RAM allowance that could cause a similar problem?
I don't believe that Express on it's own will cause the problem. As I said, (aside from specific features) the primary difference is the CPU and memory scalability, which does not appear to be an issue.
If you restart the SQL Server instance, does it perform better?
Have you checked to see that the tempdb and transaction log files are not autogrowing?
Ok, the problem was management studio trying to talk to a remote server it seems -- The script running through a web application was down to a few seconds on Express and on Enterprise.
However, it is still slower than pre-transfer by a factor of about 3 - I suspect this may be due to either a) SQL Server 2005 being slower than 2000, or b) Installing a backup SQL Server 2000 database onto 2005 and changing compatibility level leaves the database not operating as efficiently as it could be under 2005.
I'm giving you the points for your time, thanks.
Business Accounts
Answer for Membership
by: NightmanPosted on 2007-01-25 at 14:02:06ID: 18399780
1. update statistics on all tables.
2. check your index fragmentation (if necessary rebuild indexes - when noone is online)
3. Check that there is enough memory allocated to SQL.
Standard will only be faster in that it can leverage more memory and more CPU's. Developer actually behaves as Enterprise.