Signs You Need SQL Server Enterprise Edition

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.

Resource Governor

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.

Online Indexing

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!

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.

3 thoughts on “Signs You Need SQL Server Enterprise Edition

  1. You need resource governor on SQL Server VMs on Azure to stop Recovery Service Vaults (or backup in general) flat-lining your disk bandwidth, causing your witness server to lose connectivity and failing over, thus killing the backup – ’til it kicks off again because it failed AND…

    With resource governor you can dial the IOPS down to whatever your machine can cope with (1 IOP = 8MB for the purposes of Recovery Service Vaults) so your 5000 IOP, P30 SSDs, really are too fast for backup.

    Of course that’s all well and good but backup compression will still take 100% of CPU because that process doesn’t run under the same user as the service that runs the backups.

    Nike Neugebauer had a good post on the subject that proved I wasn’t going mad:

    Admittedly it’s nice to have all the backups done within the hour rather than waiting for some overworked backup exec server to deign to do the job on schedule – but having to go to Enterprise to use backup safely…

Comments are closed.