Major performance difference between SQL Server Developer and Standard Edition

All we need is an easy explanation of the problem, so here it is.

We are experiencing an interesting performance issue. We found a query that runs in around 6 seconds in our development environment. However, when deployed to production (same data / same hardware) it takes around 36 seconds to execute. After doing comparison and side-by-side testing (confirmed that all settings are identical), it appears that the only difference is that development is using the developer edition and production uses standard (both SQL Server 2019 fully patched).

I suspect some of the features that are available in development (i.e., Enterprise edition) are causing the performance benefit in development, but I don’t know where to go from here. I am fairly confident in this because I provisioned a separate server, did the test in development (6 seconds), reinstalled with Standard, and it went to 37.

What feature(s) are likely causing this difference? I am hoping knowing that it will help me performance tune this query more (it is pretty ugly at the moment) in Standard Edition since we can’t afford Enterprise in production.

Development Execution Plan: Plan

Production Execution Plan: Plan

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

On Dev, you get batch mode. Dev is not limited, and it has same feature set as Enterprise. One of them is batch mode without involving columnstore indexes. In batch mode, the operator works over approximately 1000 rows at a time, instead of one. This alone was enough to get the simpler plan fast enough on Dev Ed, evidently.

This simpler plan due to batch mode results in a more efficient clustered index scan operation to occur over the ~2.8 million rows that need to be processed in this query from your MAT_VIEW_ADVERTISING_TARGET_BASE, whereas a clustered index seek is occurring on the same amount of rows in the less optimal plan in Production. A batch mode clustered index scan appears to be more fitting for your data and the amount that’s being processed from the aforementioned dataset.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply