Hitting the limits of your SQL Server: Time to scale out?


Nov 28, 2017 AT 06:03 PM

MaxParallel Microsoft SQL Server

Contributed by Augie Gonzalez, Director of Product

Marketing, DataCore Software

 

When complaints about slow systems reach a fever pitch, there’s no ignoring them. You’ve exhausted all the hardware options at your disposal. Maybe now is the time to scale out your SQL Server. It’s a trendy thing these days, isn’t it? Just like they’re doing in the cloud.

Scaling Out Is Harder than It Sounds

In theory, scaling out seems easy enough, and has definitely worked well with web servers. A little help from load balancers to direct traffic and you’re all set. Or maybe not. Don’t make the mistake of underestimating the level of effort and expertise necessary to partition databases (1) from one machine to two. Check out these articles from a variety of creditable sources and you’ll see what I mean.

Tips to Consider Before You Scale Out

There are a lot of considerations before you scale SQL Server. Should you shard (2) the database horizontally using ranges of rows -- only to find that you’ve aggravated the hot spots? Or maybe you should split it vertically. Whichever you choose, some significant upfront testing and modeling must precede these decisions. This is when the hard work truly begins.

Before you do anything, consider some less dramatic measures. Specifically, how can you squeeze more from your current gear--especially when you notice hints of unused headroom, like poor CPU utilization.

This can signal chokepoints – possibly from insidious locking that causes queries to stall and reporting completion times to lengthen. PAGEIOLATCH waits can also reveal an opportunity to get more from your existing hardware.

The Secret to Getting More from Multi-Core

It may be a just a matter of tapping more of the concurrency possible from your multi-core processors. We’ll explore some ways you can do that in our upcoming webcast, “SQL Server Parallelism: Putting all those Cores to Work.”


To learn more, tune in to our webcast on Dec. 13th, “SQL Server Parallelism: Putting all of those cores to work” or click here to experience a FREE 30-day trial of MaxParallel!

 

 

Sources:

1. Microsoft 

2. Microsoft