Thanks for attending my session! You can download the PDF slides, subscribe to my weekly email newsletter, and check out our upcoming webcasts.
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.
Top 10 Developer Mistakes That Won’t Scale
Watch my TechEd 2010 presentation on common issues I see when doing SQL Server performance tuning:
StackOverflow’s Shared Scaling Tips
- Blog.ServerFault.com – 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.
- Blog.StackOverflow.com – more general blog that includes the StackExchange Podcast. There’s some scalability info in here, but not as much as the ServerFault guys.
Basic Preparations to Scale SQL Server
- Caching Products – the fastest query is the one you never make. When your app needs data, check the cache tier first, then only query if the data isn’t in cache. Caching options include Redis, Memcached, and Windows AppFabric Caching.
- Lucene Full Text Search – this open source product is cheaper, faster, and more scalable than SQL Server’s built-in full text search. It’s much easier to scale this out with multiple boxes rather than trying to scale SQL Server. There’s also Lucene.NET for .NET folks.
- Adding Reliability to Your Infrastructure – all other things being equal, the more moving parts you add, the less reliable your system gets.
- Performance Considerations of Datatypes – study by Michelle Ufford about the impact of choosing the right datatype.
- 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.
- OCZ Z-Drive Review – StorageReview covers this PCI Express drive in depth. AnandTech also covered it.
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.
- 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.
- Cache-Warming Query – wildly unsupported, but gives you a rough idea of how to select data from all of your indexes in a particular database to warm the buffer pool. Doesn’t work with filtered indexes, and make sure you have enough memory to cache the entire database.
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.
