In our new 2-day hands-on lab course, Performance Tuning By Example, students have a series of performance tuning goals they have to accomplish. For example, in one scenario they have to fix the performance problem only using indexes, and in another they’re only allowed to change code (but not indexes.)
In each lab, they have just one hour to do their work. It’s way harder than it sounds, so I give them a time budget for starters. Here’s Lab 3’s budget – keep in mind that by now, they’re already used to running & measuring a workload very quickly, so they don’t have much time to do that portion:
Sketching out a high-level project plan of what you want to do when performance tuning helps you understand the depth to which you can go in the limited time you’ve got.
If someone hands you a 1,000 line stored procedure and asks you to make it go faster, think about:
- Getting a working set of parameters to run it
- Verifying that the parameters take the right amount of time to run in development (for example, make sure it’s as slow in development as it is in production)
- Measuring the code to see which part of it is consuming the most resources
- Reading through that part of the code to get a rough idea of what it’s doing
- Making a hypothesis about why that part uses so many resources, and what you could do to cut that resource consumption
- Testing that hypothesis – writing the code, and testing the performance difference
- If it works, getting other parameters for the proc and making sure their performance doesn’t get worse
- Testing the validity of the data output (making sure it still returns the same result)
After you’ve done this a lot, you’ll understand how much time each of those steps takes for your particular application, and you’ll be able to do better work estimates. However, when you’re just getting started, a lot of those can be shots in the dark.
When I first got started building the labs for this class, I thought I’d have to put together complex scenarios with hundreds of queries running concurrently, making students sift through all kinds of conflicting data in order to find the right next steps. The more I ran through the labs myself, though, the more I remembered just how hard this stuff is when you’re facing a database you may never have seen before!