One of the things I love most about my current position is the fundamental shift in the types of optimization I’m doing. Early on in my career I was concentrating on queries that would run once an hour, and cover thousands, sometimes tens of thousands of records per hour. I think that’s a great place to start learning optimization.
You know how often each batch will run. You also know how large each back will be. As a result, you know how long you have to complete each batch. When you could get your queries below a minute, most of management was impressed. I got the bug early on to push harder and harder to get those query times even lower.
I succeeded mostly by understanding query hints, and basic indexing. Things I covered in my SQL 101 and SQL 201 series.
But after I’d mastered that, I wanted to master shorter acceptable run times. I moved to an environment where I had to strike a balance between read times and write times. An environment with thousands of readers, and hundreds of writers all hitting the same tables at once. I had to learn the balance between adding a new index, and architecting the optimal data structures.
I just realized I haven’t written any articles on that. I’ve written a task to myself to start writing about those!
After a while I had succeeded at building solutions that took advantage of every special feature SQL 2005 would give me to strike this balance between my consumer users and my contributing users. It was a blast!
During the years, I would move back and forth between these two types of optimization, but only recently have I found a third style of optimization. In my new environment We’re talking hundreds of thousands of executions per hour. We’re now talking about queries if they aren’t running in the microsecond range, the overall effect can add up quickly.
When I came in nearly every query was in the sub second range. Very good work, considering none of the developers had any training in the internals of SQL Server. Over the first few days I found that several of the queries could be improved with covering indexes. I’ve introduced the developers to this, and it’s been helpful to them.
I’m now going to work towards optimizing their database for this high level of executions per second. We’ve got to look at separating the analysis workload from the transactional workload. It’s going to be an awesome challenge. I can’t wait to apply everything I’ve learned so far, and see how much of it has to change for this different workload.
I’m bringing you all along with me during this journey. Hopefully you’ll be able to learn along with me!