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).