It’s All A Ruse
Back at the end of 2016, a Service Pack got released along with a promise: SQL Server’s programmability surface area would be equal across all editions.
Meaning that features which were previously paywalled behind $7k cores could now be used on less expensive editions.
A lot of people were happy about this, because
- You could use features without checking edition first, which cut down on code and potential errors
- Developers were less likely to use something in developer edition, which later couldn’t be rolled out to standard edition
In other words, the “developer edition” issues were solved.
Except they weren’t, because performance is still far from equal.
Sing Along With Me Now
Let’s check out RDBMS scalability and performance for some gotchas that can sneak up on you if you’re using Developer Edition to Develop, and Standard Edition in Production.
Here’s a list of stuff that you might turn on, or benefit from when performance tuning in your development environment, that wouldn’t translate to Standard Edition:
That’s the list of stuff that’s a straight up “no”. On top of that, there are some things that aren’t quite a “no”, but aren’t quite a “yes” either. Batch Mode queries have a MAXDOP of 2 in Standard Edition, and lack SIMD support. That’s bitten me in the past when doing performance tuning work.
My Fair Lady
Some of these features are opt-in, so if you turn them on in Development and they don’t work in Production, that is your fault and your fault alone.
Others have feature switches, many of which are database scoped configurations, so you can disable them for an entire workload and not have to go mangling a bunch of queries.
But then there are others which you have no control over:
- Batch Mode DOP 2 limit
- Large page allocations
- Read aheads
- Merry go round scans (Advanced scanning)
Those are all things that seem slight, or easy to avoid, until you have performance gaps between Dev and Prod, and figuring out why takes seriously in-depth, and often frustrating trouble shooting steps.
Calls to have a “Standard Edition” switch for Developer Edition have been around, I’m told, forever and a day. I’m not going to open a new User Voice item in dev/null asking for it. Just be aware that your performance test results may not pan out when you deploy from Developer Edition to Standard Edition.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.