It has been almost 19 years since I started working with SQL Server and for over 14 years I have focused on SQL Server Performance Tuning. I have been fortunate to have seen nearly every single problem in this area. Every single day I meet a new client and work on their complex deployments as well as configuration issues. I see all the poorly written queries and I am tasked to improve the performance of those queries. When I start to believe that I have seen all the SQL Server Performance Tuning issues out there, Microsoft releases a new product and I get to learn and solve new issues. I absolutely love my job using Comprehensive Database Performance Health Check, which has inspired me to write Performance Monitoring Week.
Monitoring Week
During the health check engagement, I am often asked the following questions:
- How do we monitor our SQL Server’s performance proactively?
- How do we know if any users have changed configuration that is critical to the business?
- How do we secure our database and comply with audits?
- How do we know that our workload has changed and what are the next steps to accommodate the change?
- How do we know which indexes to create and drop?
- How do we know that our SQL Server is about to become unresponsive?
- How do we do increase our capacity for planning?
- When do we increase critical resources like CPU, Memory, and IO?
- When should we tune queries and when should we add resources?
Well, there are many more questions that I often receive during Comprehensive Database Performance Health Check. My goal is for my customer not to have to hire me every single time they face a performance-tuning issue or any monitoring issues. I aim to coach them so that they do not have to hire me again and again. I have written my consulting philosophy here Consulting Wrap Up – What Next and How to Get Started.
SQL Server Performance Monitoring Tools
Here are the tools that I use almost daily to help my customers learn how to use them with their SQL Server. There are many different tools out there, but these are the tools that I use frequently and STRONGLY recommend.
The tools, in no particular order:
- Database Performance Analyzer – Blog and Download
- SQL Diagnostic Manager – Blog and Download
- SQL Monitor – Blog and Download
- Spotlight Cloud – Blog and Download
- SQL Plan Warnings – Blog and Download
Once you get out of a critical situation, it is important that you put in a place a SQL Server Performance Monitoring solution which can help you to proactively manage your SQL Server’s health and alert you in advance before any disasters. Additionally, a good SQL Server Monitoring tool will give you clear actionable guidance to recover from disastrous situations.
Reference: Pinal Dave (https://blog.sqlauthority.com)