How To Tune Performance - Chapter 1: To-know Tools

General Performance Tuning Chapter 1: To-know Tools

This series of articles will cover the first steps you should do when you enter to a new environment, aiming to improve overall performance. Since there are endless things and approaches to do so, we will not only present the tools, methods and tips, but will also divide the possible scenarios you might run into and how to deal with any of them. Each chapter will focus on a different aspect you should be familiar with when facing a performance issue, either a current crisis or a general “slowness”. This guide is aimed for junior-intermediate experience level DBAs but might be interesting for experts as well.

Chapter 1: To-know Tools

For our first chapter, let’s talk about some tools that you should be familiar with. We will not dig into these tools since each one of them can fill a complete series of blog posts, but give you a general idea of the important tools you should know to be able to handle any scenario.

1. Execution plans – A graphic diagram, explaining the workflow conducted by the Query Optimizer, to perform the executed query. By analyzing the execution plan flow, we can understand how the query was executed, detect its bottlenecks, and even find deeper issues that might affect more processes. There are two approaches to execution plans: Left Top -to- Right Top – giving you the flow conducted – the flow in which the tables were read data was accumulated. Right Top -to- Left Top – giving you the flow “calculated” – the flow of what is required for each action.

2. Statistics IO/Time – These are session-level configurations. Using Statistics IO ON/ Statistics Time ON provides us extra information after executing a query in the “Results Set” tab, that helps to accurate the findings and recommendations.

3. Monitoring Tools – There are plenty of monitoring tools/methods. 3rd parties, inside of SQL or self-implemented. When configured right, it can be the key to solve performance issues and more. They become most critical when the issue is not currently occurring. Without having any, it is extremely hard to find out what happened in the past.

4. DMVs – Dynamic Management Views are built-in system views providing us real-time/aggregated data, serving us for both proactive actions and immediate resolutions for current issues happening. These can shed light on anything that is happening now, providing you with any data you might need.

5. Last but not least - External Tools – Regarding 3rd party’s tools and open-source solutions - might often be helpful. It is important to understand whether the organization has any. It is also useful to know a set of those to recommend when it is needed.

These are the basic tools you must know/be familiar with, to face performance incidents.

Not always you will need them all, but with all of them, you will be able to provide an end-to-end solution, understand the case, solve it, detect the root cause analysis and provide a long-term solution to avoid having this problem again.

The next Chapter will be – Analysis, Questions, Detection.

It will cover the first steps you do before you start doing anything.

Stay tuned!