SQL Server 2017 Express faster than Standard Edition 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.

These are the plans for Express and Web Edition. They look the same to me.

Here are the timings as measured by SSMS (latest):

Express



Web Edition



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

In the execution plan of the query that performs worse we find the following wait stats:

  &lt;Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="280" WaitCount="27" /&gt;
        &lt;/WaitStats&gt;
        &lt;QueryTimeStats CpuTime="14" ElapsedTime="295" /&gt;


For the 295ms that the query executed, 280ms was waiting on ASYNC_NETWORK_IO.

As a result, 14ms cpu time &amp; 15ms elapsed time without these wait stats seem reasonable to me.

These details in the actual execution plan mean that the issue is not directly related to the performance of the instance itself, but to the processing of these rows.



ASYNC_NETWORK_IO

Some parts of a previous answer on why ASYNC_NETWORK_IO occurs by Tom V

Which refers to a post by Thomas LaRock


  The ASYNC_NETWORK_IO wait indicates that one of two scenarios are
  happening. The first scenario is that the session (i.e., SPID) is
  waiting for the client application to process the result set and send
  a signal back to SQL Server that it is ready to process more data. The
  second is that there may be a network performance issue.


Most of the time I see ASYNC_NETWORK_IO happening it is due to the application not processing the rows fast enough (application design / app server under heavy load / stress). 

After validating the application, the network would be the next suspect.