Want your app to be able to handle more users? Here are the tips I’ve gathered over the years on how to make your application scale up with SQL Server.
Basic Preparations to Scale SQL Server
- Adding Reliability to Your Infrastructure – all other things being equal, the more moving parts you add, the less reliable your system gets.
- 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. I explain what counters to look at and how to analyze them.
Lessons Learned Scaling StackOverflow
StackOverflow gets over a million pageviews per day with a SQL Server back end consisting of just one commodity hardware box. Here’s some of the lessons we’ve learned along the way.
- Capture & Test Every Query Before Upgrading – we upgraded to fix one problem, and ended up with a different problem because 2008 handled a specific kind of query differently.
- Scaling Full Text Search in an OLTP Environment – we ran into a problem with SQL 2008’s new FTS and got help from Microsoft.
- High Scalability Profile of StackOverflow – talks about the architecture and hardware in use.
Scaling SQL Server in the Cloud
- SQL Azure is the Flat Pack Database – Microsoft’s SQL Azure is touted as a scalable cloud-based database – but what does that really mean? I explain.
- Bad Storage Performance in Amazon EC2 – cloud doesn’t always equal speed. Benchmark test your Amazon EC2 SQL Servers to find out why.
More SQL Server Scalability Resources
- My Bookmarks Tagged PerformanceTuning – I share my bookmarks online, so you can see what resources I’ve found useful.
- My Blog Posts Tagged PerformanceTuning – including my weekly link posts with performance tips.
