But Postgres Is So Much Cheaper
There’s a lot of cool stuff in Enterprise Edition of SQL Server, which is probably why it costs $7000 a core.
When’s the last the price went up, though? Hm?
Real Availability Groups
HA isn’t my thing, but a lot of people seem to be into it. I still prefer Failover Clusters most of the time, but for folks with a deeper interest in self-flagellation, Availability Groups are there for you. In Standard Edition, you don’t get the full fledged technology though. There are a lot of limitations, and most of the time those limitations are so stifling that people bail on them pretty early.
Full Batch Mode
SQL Server Standard Edition hobbles batch mode pretty badly. DOP is limited to two, and there’s no SIMD support. It’s totally possible to have batch mode queries running slower than row mode queries, because the row mode queries can use much higher DOPs and spread the row workload out.
I’d almost rather use indexed views in Standard Edition for large aggregations, because there are no Edition-locked enhancements. You’ll probably wanna use the NOEXPAND hint either way.
All The RAM
Memory is just about the most important consideration for SQL Server hardware. It can truly make or break a workload. Sure, CPU can too, but without sufficient memory it’s unlikely that you’ll be able to push CPUs hard enough to find out.
With Enterprise Edition, you can pack a server with as much memory as you can download. I spend a lot of time trying to explain this to people, and when they finally listen, they’re amazed at the difference.
I don’t like this for much, but I absolutely adore it for capping memory grants lower. Kind of building on the same points as above, memory is shared between the buffer pool and query memory grants. By default, any query can come along and ask for 25% of max server memory, and SQL Server is willing to let up to three queries doing that run concurrently.
That means ~75% of your buffer pool or so can get eaten alive by query memory grants. And lemme tell you, the optimizer can be really bad at guessing memory grant needs. Really bad.
If you’re at the point where you need to think hard about some of the stuff I’ve already talked about, you’re probably at the point where your data is reasonably big. Creating new indexes can be tough if you need to do it on Standard Edition because a lot of stuff can end up blocked while it’s happening. That means index changes have to wait for maintenance windows, which makes it harder to solve big problems on the spot.
Thanks for reading!
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.