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.
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.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
Database DevOps Demo Webinar
Learn how to automate your database deployments alongside your app code in this free demo webinar. Register now
What’s the top challenge faced by SQL Server professionals in 2018?
Learn how 626 SQL Server professionals monitor their estates in our new report on the State of SQL Server Monitoring. Discover the challenges currently facing the industry, and what is coming next. Download your free copy of the report
In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. More »
Power BI has been available for a number of years, but new functionality is added regularly. In this new series from Robert Sheldon, you will learn about the latest developments or get started with Power BI if you are new to it. Robert provides an overview of Power BI, the services and tools available in this article. More »
Prepare for Microsoft Exam 70-765–and help demonstrate your real-world mastery of provisioning SQL Server databases both on premise and in SQL Azure. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
In SQL Server 2017, what are the limits for FKs on a table?
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.