SQL Server 2017 Express faster than Standard Edition [closed]

Question

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

Answer 1

Wait statistics

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

  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="280" WaitCount="27" />
        </WaitStats>
        <QueryTimeStats CpuTime="14" ElapsedTime="295" />

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

As a result, 14ms cpu time & 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.

Answer 2

Have you checked the network speed on the various servers?

I had a similar problem that we traced to a patch cord running at 100 mbps rather than 1000 mbps.

Answer 3

It shouldn't be faster. If you have the same data, on the same machine, on the same network, and you're getting the same execution plan, it seems like it'd be a configuration setting in the software itself. Have you tried this:

--turn on statistics--

Set statistics time on;

set statistics IO on;

Go;

--then run your query--
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

--do this for both and use to compare.  

The main difference in performance between the two versions comes in the ability for standard to allocate more resources towards SQL server. I'd double check all the instance properties and the database properties but if you went standard you could go much faster potentially than express. If you're going to run tiny queries, then you don't need to worry about it but if you run bigger ones, you are going to run into paging issues. https://www.microsoft.com/en-us/sql-server/sql-server-2017-editions

Think of it like comparing the speed between a crappy car and a standard car. They can both go 20 mph but they can't both go 120 mph. The crappy one might top out at 70 mph.

Answer 4

I am of opinion that there is slight difference in the number of logical reads between two plans. If you notice number of rows read between express and web edition, you are reading 435 rows in express whereas 390 in the standard edition(PK_Client) and there is difference in number of select statement also as per your screenshot of "Client Statistics". Ideally there should be no(negligible) difference in between performance of express and standard edition however it seems there are different number of rows and different settings in between these two.

Time is not the best parameter to check the performance difference, if you check number of pages read and compare them, that should give you more meaningful result. Time depends on so many factors at server level - possibly CPU is heavily loaded while you were running your query.

Number of pages won't change unless and until you execute maintenance job(reorg or rebuild) and hence it is more reliable for performance than time. You should also check if the table at standard edition is heavily fragmented and hence requires more pages(time) to fetch the result in comparison to express edition.

As mentioned by Mr. John-Henry Lochbaum, please include IO statistics before running your query and check.

Hope this helps.