SQL Server Tuning Skills

Performance tuning a SQL Server instance, database, or even query is a skill that most of us could improve. In fact, I see that the query performance presentations are often the most popular learning items at any event. I’ve seen quite a few people asking for more tuning talks in different areas, which is a good thing. I think most SQL Saturdays could schedule a different tuning session every hour, or even repeat the same one a few times, and the room would be mostly full each time.

Learning to look at the various possible problems in a SQL Server is both a science and an art. There are good methods, such as waits and queues, for examining where an issue occurs. There are basic techniques for looking at execution plans and then making changes to indexes or T-SQL. There are different options for queries, SET values, and more that will affect your query or even all your queries.

Sometimes it seems there might be too many options for a beginner to decide how to begin examining a system, especially one that is having issues. I can’t cover all of the issues in this short piece, but I can give you some guidance that might help. First, make sure that you know how to gather information about your issue. We have forums at SQLServerCentral to answer your questions, but you have to do some work. Gail Shaw wrote a great piece on how to post performance problems.

Ultimately, you need to understand the recommendations that we may give. It’s helpful to learn about indexes, and the differences in the various types (including Columnstore). You should also have a baseline so that you can measure the impact of your changes. How do you know the problem is fixed? Maybe the user gets lucky with a quicker experience when you tell them you’ve changed the system, but will they call again soon? That second call might be worse than the first one. Even getting a baseline of the poor performance now will help you understand (and prove) that the system is running better.

You also need to understand what not to tune, and which items to ignore. I thought quite a bit about this after reading Paul Randal’s post on wait types to ignore. This is part of his 101 series, which are a good way to start improving your skills. Performance tuning takes practice and knowledge, which takes time to acquire. Even if you’re fighting a problem today, try and learn a little something about how you get your system to run better.

I’d also recommend that you look to continue your education over time. Take advantage of those sessions at local events, like user groupsvirtual groups, and SQL Saturdays. Take advantage of pre-cons at larger events, which pack lots of learning into a few hours. If you go, take a paper pad, not a laptop, and take lots of notes. If you can’t get away, maybe you can lobby for some tuning classes from sqlSkillsBrent Ozar Unlimited, or even self-paced courses on Pluralsight.

Learning to become better at tuning is an ongoing process, and one that requires some investment. Perhaps some money, but definitely some time and practice are necessary. Don’t let this be a daunting task, but one that’s a journey. Your goal is to get a little better each time you work on this skill, not to be an expert that knows it all. I’m not sure anyone gets to that point, but you can become as good at this skill as any of the presenters you watch. It might take you longer than it took them, but you’re also able to build on their work and learn from their knowledge. Take advantage of that opportunity.

Steve Jones

 

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

2 Responses to SQL Server Tuning Skills

  1. Chris Fair says:

    This is a post with some really great wisdom for those of us just starting out. However, one of the things I’ve noticed in most of those tuning presentations is that they all assume you have access to change the queries or the developers that can. I’d say 95% of the databases I deal with are vendor databases where I can maybe see the queries (via my monitoring tool) but don’t have the ability to change them at all. I’ve looked extensively for training on tuning when I can’t access the queries and have come up quite short. Brent Ozar (https://www.brentozar.com/training/) has a few classes like that, but my budget (both work and personal) is unfortunately insufficient to the point where I cannot afford to take them. I really need access to free or low-cost training. I’ve been to a few SQL Saturdays, but I haven’t come across very many sessions that deal with performance tuning without query access. Nonetheless, I love SQL Server, being a junior DBA, and improving my skills, so I have no problems looking and attending free or low-cost training when I can find it. And your post has been helpful in pointing me towards a few more options I haven’t come across yet. Thank you!

    • way0utwest says:

      Chris,

      Good points. I’ll write a bit more, but in short, this is a class that looks at tuning with indexes only. No code changes. In one lab we can change code, but the challenge here is to tune with no app/code alterations. It’s a good way to look at this.

      Brent is expensive, arguably worth it, as are other classes. Doing this on your own, or with random articles/videos, is possible, but hard.

      I’d like to do a basic tuning one, and maybe an “index only” one is something to give at some SQL Sats. Ultimately, I need you and others to learn some things and give your own presentation. If we get more people showing some simple techniques, we all slowly learn better.

Comments are closed.