What could explain SQL Server Express Edition being much faster than Web / Standard Edition?
These are multiple VMs in cloud hosting. They all experience the same problem. Some are on SQL Server 2012, others on 2017 - same problem. All machines have latest updates.
Machines are good enough for the job: 8G 4 cores and DBs are small in size less than 1GB of data. Same machines. I even tried have both Express and Web Editions on the same machine with only one instance enabled at a time, same problem.
It does not matter if the web application is run locally in the same machine or remotely accessing SQL sever though network, same problem.
The DBs have exactly the same data. I used a backup of Express and loaded it on a Web Edition server. This is a VM with SSD Drives. Install was as standard as possible. I compared settings in SSMS looking at server properties. Files are small and in the standard locations.
Queries are multiple in forms and complexities all run about half the speed in Web Edition. This is a repeatable problem not just once off.
It is not specific to Web Edition and the type of server as this was replicated on development machines on Express VS Developer Edition.
SQL Server settings appear to be the same.
Multiple people have looked at it and we cannot figure out why.
I was told to give example:
select top (500) * from UserLog u join staff s on s.Username = u.username join Forms f on f.PageFile = u.PageFile left outer join Client c on c.CID=u.CID order by u.id desc
Execution plans are exactly the same.
Here are the timings as measured by SSMS (latest):
Over multiple tries Express is 50% faster on this very simple query.
Is there a minimum DB size for non-Express Edition? (this would not make sense...)