gotchasqlModerate
Major performance difference between SQL Server Developer and Standard Edition
Viewed 0 times
majorsqlandstandarddifferencebetweenperformancedeveloperserveredition
Problem
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
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
Solution
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
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.Context
StackExchange Database Administrators Q#295233, answer score: 12
Revisions (0)
No revisions yet.