How To Tune Performance - Chapter 2: Analysis, Questions, Detection

This series of articles will cover the first steps you should take when entering a new environment, aiming to improve overall performance.

Since there are endless things and approaches to do so, we will present the tools, methods, and tips and 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 at junior-intermediate experience level DBAs but might be interesting for experts as well.

In our previous post, we've discussed To-know Tools you should be familiar with. And this chapter is:

Chapter 2 - Analysis, Questions, Detection

There is no end to performance-tuning. You can dedicate hours improving something that will turn out to be irrelevant and impact-less. Therefore it is crucial to dedicate enough time to understand the actual needs of the customer/environment. For example, what "hurt" the most, what is the scenario you are facing, is there a specific problem set as your goal, or is your goal a proactive improvement to deal with more traffic in the future, even though everything is okay? We've all been there, either as a proactive action or as a result of complaints, finding a massive query/procedure to improve, dedicating hours to it, reducing it on several scales, and eventually finding out it was not the right thing to focus on, not interesting to anyone, not the root cause, etc. This is why asking questions (the more, the better) will be the first step until you fully understand what is going on. It is better to do so with someone who knows the issue, is familiar with the environment, and can shed some light on what is going on/worth focusing on. It is also better to be prepared and make a list of things you'd like to ask, and not via mails. To help you out, here are "for instance" questions that you must know the answers for (either for a "specific current issue" or a "general performance tuning")

1. What is the exact issue you have? Is it a current issue or a "general" not-good-enough performance?

2. A specific process/ a general problem?

3. When did the downgrade of performance start? In which frequencies?

4. SQL Server version? Edition?

5. Server Specs? On-premises/Cloud?

6. Are there other apps installed on the server?

7. Users, traffic, workload, OLTP/Data Warehouse

8. Maintenance? (Backups, index optimization, statistics updates, integrity checks)

9. Is there a specific problem/ process/ procedure/ query that should focus on a proactive act?

10. Have any changes happened lately?

11. Any changes/increases in sizes expected soon?

12. What "hurt" the most?

13. How is this issue affecting you? The app? The business?

14. Do you have any monitoring tool (3rd party) installed/ do you use any inside-SQL-Server tool to monitor the environment?

15. Any unique architecture? DR/HA?

With these questions, you will have a general idea of what is happening and how you should approach the case.

Thank you for reading. The next chapter will be Chapter 3 - Past, Present, Future – Different Strategies. We will discuss what should be considered, depending on when did it/will it happen.

Stay Tuned!