Take it from me, performance tuning ain’t easy. To do it well you need to use all of your technical skill, plan strategically, and communicate your recommendations effectively. You also need to inspire adoption. After all, what good is a plan to change a system if you can’t convince anyone to go along with you?
We performance tune all sorts of environments— from OLTP SQL Servers running 30K+ batch requests per second to multi-terabyte warehouses. We use a wide base of knowledge about database systems to performance tune these environments, but we always make sure that our process avoids three strategic mistakes.
Mistake #1: Specialist Syndrome
It’s tempting to take on a performance problem solo with your core team of super-technical database engineers. It feels efficient, focused, controllable. This is a strategic mistake– by doing this you exclude input from very important parts of your team.
When working with clients we bring together DBAs, developers, managers, sysadmins and business users. We show the whole team how to analyze the database environment from the storage subsystem, hardware, and OS configuration up through the SQL Server configuration and query tuning. Everyone on the team gets a broader perspective about the application environment and how bottlenecks manifest between storage, the database, the application, and the users. Everyone learns more about the problem and explores potential solutions.
We take the whole team through the process, no matter what level they’re at with SQL Server. This is tricky, because it requires explaining complex topics in everyday language. We do this because it’s important, and everyone is completely capable of getting these concepts and understanding performance bottlenecks. Everyone has different pieces of the puzzle and will play roles in short or long term changes.
I wish I knew how many times I’ve heard a business user say, “You guys are still running that? We stopped using that last year.” Unfortunately, I’ve lost count.
Mistake #2: Doing Daily MRIs
Database administrators and developers LOVE routines. They naturally crave activities they can do daily. Performance tuning works best in larger phases, though: focus on a high intensity period of analysis. Then follow it with longer periods of implementing changes, while measuring adjustments in the system.
We work with clients in four day bursts of high-intensity SQL Server workouts because this pattern is effective. We first identify the big pain points we’re going to resolve. We then work through a wide ranging discovery process, put together recommendations, and take the group through training as a recap. We explain context around bottlenecks in the database server and how they relate to the client’s pain points. We plan out recommendations for the next week, the next month, and the next quarter to address those pains.
Following this intense analytical period the client teams disperse. People take what they’ve learned back into their ordinary jobs and execute on their tasks.
Think about performance tuning as a big medical exam: you’re doing bloodwork, getting XRays, and thinking deeply. You reassess your big pain points periodically and use that big burst of activity to communicate across teams and build a larger plan. Doing this intermittently allows you to lead your system to change. Trying this daily turns you into that person always sending irate emails.
Mistake #3: Old Medicine
Whether or not you’re calling in a consultant, you need another pair of eyes on your performance tuning techniques. We’ve refined our process across many clients, and we’re always thinking of new ways we can convey ideas and strategize solutions together. Inside our company, we’re always asking each other, “How can we do this even better?” Just like doctors, we can never accept that we know the best answer for all time to a pain point. We have to be open to new treatments.
Your performance tuning techniques need to evolve. This happens because of changes in your environment, software, data patterns, user patterns, and business processes. Document how you do performance tuning. This includes methods for identifying your bottlenecks and pain points as well as measuring improvement from changes in the system. It also includes defining the team, investigating pain points, planning a solution, and communicating and tracking recommendations.
How you write this down doesn’t matter. What’s important is that each time you use the process, you get feedback on how to make it better and make notes. Since you’re doing this intermittently, those notes are valuable!
If you look back at your method after a year and nothing has changed, you’re probably doing it wrong. By its very nature, performance tuning involves growth and learning. And that’s exactly what keeps it fun.