In my SQL Saturday #80 Session I spoke about how to get started as a DBA. One of the greatest advantages you can have as a DBA is setting up a fully featured monitoring solution. I mentioned three different solutions: Redgate Monitor, Confio Ignite, and SQL Sentry Power Suite.
I mentioned the pros and cons of the three solutions, and mentioned that I had chosen to go with SQL Sentry. I wanted to give a little more detail on why I made that call.
All three have some form of performance monitoring. But when I opened the Performance Dashboard on my server for the first time, I was blown away by what I saw. In one screen I could see:
- CPU utilization that was happening right then
- Waits that were happening right then
- I/O for both reads and writes that where happening right then
The amount of information at first just floored me. To be honest, it still intimidates some of my business users when they look over my shoulder. But seeing what’s happening right now is only useful some of the time. I can also go back in time and see what the usage was at any point in time after installing the monitoring solution.
I can also change tabs and pull up an image of how data is moving about my system. This has helped me point out when I need to move data files to separate LUNs in order to maximize read and write speed and eliminate contention for I/O resources between databases.
But the performance advisor isn’t the biggest reason I went with SQL Sentry. It was the Event Manager. It’s a calendar of all the events that are happening, and have happened on my SQL Server. With it, I can see:
- Deadlocks and when they occurred.
- Jobs when they run, when they fail, and when they run long.
- I can see when Queries are consuming a lot of resources
Being able to see what was happening on a server at the time of a deadlock can help figure out how to resolve the deadlock. Let’s say you’ve scheduled a very large ETL Import process to run at the same time you have an even larger ETL export, guess what… they’re going to clobber each other. If you schedule the export to begin after the import has completed, you’ve just fixed the deadlock’s root cause!
This calendar of events is the number one reason I went with SQL Sentry. But before I let you go, there’s one more reason to go with SQL Sentry. SQL Sentry Plan Explorer integrates with the Event Manager.
When you see top SQL events happening on your server. Double click on the event. It’ll load that query into Plan Explorer, and you can really dig into why that query is so costly. You can even break down your cost to just I/O, or just CPU, depending on what you want to optimize for. You can also sort the operations by that cost in a table view, rather than having to drag around a graphic of your query.
Each one of these products on its own are great, but when you put them all together, it’s beyond awesome. It let’s you do more than just react to problems on your SQL Server, it let’s you get ahead of them. You’ll see problems before your users. And when you can get to that point, you job is completely different. It’s almost easy.