Scale Up or Scale Out? Scaling SQL Server

Jes scaled up because she really wanted to dunk.

Jes scaled up because she really wanted to dunk.

Want your app to be able to handle more users? Here are tips, tricks, and links to comprehensive references on how to scale SQL Server– either Scale Up or Scale Out.

Do You Really Have a Scale Problem?

It could be that you just have a bad implementation. First, make sure you’re not wasting your time.

Scale Up

Want to grow upwards, using big hardware, big tables, and lots of queries running on a single instance?

Scale Out: Availability Groups, Sharding, and More

Instead of just building upwards, what are your options for distributing your data outwards in SQL Server?

Scale Out: Caching

Application caching sounds great, but is it really all that? (Well, yes, it actually is.)

Testing and Performance Monitoring

You need to test and monitor no matter how you scale, but you need to become a serious pro at performance monitoring when you scale up.

  • Random vs Synchronized Load Testing – users sometimes hit your application in waves, with several people doing the same thing at the exact same time. Find out if that will cause a problem rather than just having a bunch of people randomly click around in your app.
  • Using Perfmon for Performance Tuning SQL Server – if you want to scale, you have to find out what bottleneck is currently holding you back from getting more performance. Brent explains what counters to look at and how to analyze them.

Lessons Learned Scaling StackOverflow

StackOverflow serves millions of pageviews per day with a SQL Server back end consisting of plain old commodity hardware with local drives. Here’s some of the lessons we’ve learned along the way.

StackOverflow’s Shared Scaling Tips

  • – the sysadmins behind scaling StackOverflow’s infrastructure. It’s not purely dedicated to SQL – it touches a lot of parts of systems administration – but there’s a lot of great information in here. For an example, start with Different Views of the Same Problem.
  • Sam Saffron’s Blog – Sam is a rocket surgeon coder who’s doing great stuff to keep performance fast.
  • MVC Mini Profiler – like running a client-side trace that can tell you which queries are slow (or running too often). To see how it can work in your application, check out Scott Hanselman’s MiniProfiler walkthrough.
  • – more general blog that includes the StackExchange Podcast. There’s some scalability info in here, but not as much as the ServerFault guys.

Scaling SQL Server in the Cloud

More SQL Server Scalability Resources

Read more on performance tuning.

Want to learn even more? We’ve got video training explaining it! In our 90 minute video series How to Think Like the SQL Server Engine, you’ll learn:

  • The differences between clustered and nonclustered indexes
  • How (and when) to make a covering index
  • The basics of execution plans
  • What determines sargability
  • How SQL Server estimates query memory requirements
  • What parameter sniffing means, and why it’s not always helpful

It’s totally free. Get started now.