SQL Server performance review.
When you’re running into performance issues with your data workload, you have a strategic choice to make.
Scale up, scale out, or tune your code
Most performance issues can be solved with a corporate credit card. You can buy more compute capacity or a faster storage infrastructure. Or you could buy more server instances, which also comes with a lot of licensing costs. But for cost efficiency and long-term benefits, there’s no competing with well-tuned code.
The cost of a 2x performance increase
Here’s a cost breakdown to roughly double the performance of a theoretical database workload:
How we did the math: The baseline is an E32-16ads v5 Azure virtual machine (256 GB RAM, 16 v-cores) in the Sweden Central region, with a 2048 GB P40 disk and SQL Server Enterprise Edition. For “scaling up”, we doubled the core count and memory but left the disk size unchanged. “Scaling out” adds another identical machine. Does not include traffic, storage IO, or discounts. Consulting fee will vary with complexity and requirements.
There’s an upper limit (both financial and technical) to how much you can scale up your infrastructure, and scaling it out dramatically increases the complexity your team needs to manage.
Good coding practices and indexing, on the other hand, leverages your existing infrastructure investment and can do miracles for your solution, while also reducing other problems like locking and blocking.
Performance tuning is the first step
Even if you’re already committed to building out or replacing your existing infrastructure, give us a call. You may be surprised at how much of a difference we can make in just a few hours of work.