So experiencing an interesting performance issue. We found a query that runs in around 6 seconds in our dev 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 dev but don’t know where to go from here. I am fairly confident in this because I provisioned a separate server did the test in Dev (6 seconds) and then reinstalled with Standard and it went to 37.
Any idea what feature(s) are likely causing this difference? Hoping knowing that 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