Outlining My Session: Index Tuning in an Agile World

I’m giving a session on index tuning at the upcoming Redgate Streamed free online conference. The conference will be held April 1-3 2020, register here and join us!

I love talking about index tuning and I know this area well, so I’m excited to put together this new session. I thought it would be fun to share my process of outlining and creating the session, leading up to the event.

The session title and abstract I wrote this week is…

Index Tuning in an Agile World

Software developers increasingly work across databases and applications, and there are fewer and fewer dedicated database developers each year. This produces a new opportunity for database administrators and developers who are interested in performance: the role of the incremental index tuner. In this session you will learn a framework for developing and tuning indexes in SQL Server, and how to apply this framework in an Agile development context.

Who is my audience? What is my goal?

Whenever I’m writing or rehearsing a session, I remind myself to think about who my ideal audience is. In this case it’s fairly broad: it’s developers or DBAs who care about performance of their code, but they aren’t yet experts at index tuning. The interesting thing right now is that often the people in this audience don’t have all of the information they need to do index tuning by themselves: from the DBA side, they typically have access to the production system but often don’t have access to the codebase, or the design of the queries. From the developer side, they often have the opposite situation: they can get to the code and change it if needed, but they don’t have regular access to production.

My goal in this session is to get all of these people started by showing an example of index tuning, and proposing a workflow in which a performance problem can be identified, a change to indexes can be suggested and reviewed, and the change can be agreed upon and deployed through the environments needed to reach production.

My initial outline

I like to begin putting together sessions with an outline of how I think the session will flow. This outline may change entirely over the course of writing the session, but it feels right to start with it. Here’s what I’m thinking we can cover in an hour:

Intro: the problem space

• Disconnected teams: developers lack production access, DBAs lack access to code and ability to change it
• Both groups often are unfamiliar with index tuning

Learning by example - the goal workflow

• Show an example query with erratic performance
• Demo: How can the problem be discovered by a DBA with production access?
• Demo: How can the problem be discovered by a developer without production access?
• Example: testing different indexes to improve performance in a development environment
• Example: using work items and a pull request workflow to propose index changes
• Gotchas: where can this go wrong?

How to improve your workflow

• What if your database code isn’t in version control yet? How do you get started?
• What if other folks don’t want to change / want to keep things they way they are?
• What about designing indexes from the ground up instead of tuning an existing system?
• References / links for learning Git, branching / merging strategies
• References for index tuning basics and further learning

I’ll be working on this session more next week, so stay tuned to see how it comes together.

I’m grateful that I get to work with others in the community in this topsy-turvy time, and that I have you as a reader. Stay safe, take good care of yourself and your families, and I hope that you are healthy and able to join us o n April 1-3 2020, for Redgate Streamed.