When MSDE is better than 2005
Believe it or not, I downgraded from SQL Server 2005 Express to MSDE and got a drastic performance improvement:
- 6.0 seconds average call time with SQL Server 2005 Express 15/10
- 1.9 seconds average call time with MSDE 18/10
The DB is identical in terms of data and indexes. The only difference is the SQL engine. I am still not 110% sure why, but as far as I can tell it is due to the different ways MSDE and SQL Server 2005 Express limits performance on my particular hardware. Or rather, lack of hardware, as I am hosting everything on a Virtual Server.
Until January this year I hosted my mail and blogs at home on a PC. It has been a valuable experience as I have learned a lot about hosting a service 24x7. But after almost 3 years I decided to host my mail and blog at a hosting provider. Aruba has a basic Virtual Server at 12 euro a month with Win2k3 64 bit, 8 CPUs, 3GB of disk, 512 MB of ram that is more than enough or me. But not for SQL Server 2005 apparently…
SQL Server 2005 Express limits performance to one physical CPU. This gives good performance on multi-core or hyperthreading CPUs. But, in the case of my Virtual Server I believe it is only using 1 of the 8 available CPUs becoming a bottleneck. MSDE, on the other hand, does not have a CPU limit the workload governor starts slowing down the database engine when more than eight operations are actively running at the same time. Which means that SQL Server 2000 Desktop Engine (MSDE 2000) is significantly faster than SQL Server 2005 Express on my “hardware“.
At least I believe this is the cause as I cannot think of any reason why my blog is suddenly 3 times faster than it used to be (after a few long nights downgrading by hand).
Only an additional idea; Sql2005 uses tempdb heavier than SQL2000 did. You know that disk access is the Achilles heel of Virtual Machines. Another point is how much memory did you allocate to SQL2005? For the CPU usage issue consider that while MSDE can use 2 CPU (Physical or Logical) at most, SQL2005Express can use 1 Physical CPU (and all related Logical instances) without having an handbrake on the query governor.
ReplyDeleteOn the other hand SQL 2005 uses much deeper CPU features that could slow everything in a virtual environment.
So my slow parade reason is:
1) Disk Access (please check the type of virtual disk you have)
2) Little Ram (and again DiskAccess)
3) Extended feature usage on CPU by SQL2005
4) Bad configured Virtual Server (HyperThreading shoul be swiched of on a vrtual server)
If I remember correctly I allocated 128 or 156 MB to SQL Server (the DB is about 50MB). That is the max I can give it as the Virtual Server is limited to 512 MB of virtual memory. Hardware limitations (RAM/HD) could be a problem as I guess SQL Server 2005 is more resource hungry than MSDE.
ReplyDelete