Play by Play with Pinal Dave: Tuning SQL Server Performance

Play by Play with Pinal Dave was my birthday gift to you on July 30, 2014. It is my new course. In it, I show how I tune SQL Server performance when the problem is real. This post tells why I made it and what each module covers.

A Birthday Note

When I was young, I always looked forward to my birthday, because everybody gave me gifts on that day. Now that I am getting older, I have almost the same feeling on each birthday, but the direction is different. I feel like giving gifts to everybody.

I have received a lot of support, love and respect from everybody, and now I must return it. So for this birthday, I had a unique gift for everybody: my latest course on SQL Server.

Why I Made This Course

People ask me how I work on a normal day. They also ask how I work when a performance tuning project is assigned to me. Many of them wanted me to explain and demonstrate my own method. They wanted to see it on a real-world performance problem.

That is a difficult task, because in the real world nothing goes as planned. Planned demonstrations have no place there. The real world demands real solutions, and it demands them on time.

A consultant who does not show their skill in the first few minutes is shown the door. It does not matter how famous they are. I faced this commonly in my early career. I learned the trick: be honest from the start, and ask the organization where I consult for fully transparent communication.

What Play by Play Is

Play by Play is a unique setup. It is not planned, and it runs step by step. It is like a reality show: a real encounter with the problem, and a real approach to solving it. I had a great time making this course.

Geoffrey Grosenbach (VP of Pluralsight) sat down with me to see what a real-world SQL Server admin does. This Play by Play focuses on SQL Server performance tuning. I go over optimizing queries and fine-tuning the server. You can find the course on Pluralsight as Play by Play: Pinal Dave.

The Five Modules

The table of contents is simple. The course has five modules, and each one builds on the one before it.

Introduction

In the introduction, I explained my basic strategies for the moment a customer approaches me for performance tuning.

Play by Play with Pinal Dave

Basic Information Gathering

In this module, I explain how I gather information for a performance tuning project. It is crucial for a consultant to show the customer their ability to solve problems. I try to resolve a small problem that gives a big positive impact on performance. To do that, a consultant has to gather the right information from the start. I demonstrate how to collect all the important performance tuning metrics.

Removing Performance Bottleneck

This module builds on the statistics collected in the previous one. I analyze the performance tuning measures and immediately start implementing tweaks, which improve the performance of my server. It is an effective method, and it gives an immediate return on the effort.

Index Optimization

Many people consider indexes a silver bullet for performance tuning. That is not always true. There are plenty of examples where an index made performance worse after it was implemented. The key is to understand a few basic properties of an index. Then you do the right things at the right time.

In this module, I describe in detail how to optimize indexes, and what is right and wrong with them. If you are a DBA or a developer, and your application is slow, this module is a must. I have some interesting stories to tell as well.

Optimize Query with Rewrite

Every problem has more than one solution. In this module, we look at another famous but hard to master skill for performance tuning: query rewrite. There are a few do’s and don’ts for any rewrite. I take a simple example and show how a rewrite can improve the performance of a query many times over. I also share some funny real-world stories.

Performance tuning is not a bag of tricks, it is honest, step by step work on the real problem.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.