It’s really been a while since I’ve written anything about SQL Server, and I suppose it’s just about that time for one of those tools that really make the life a database administrator a lot easier – and that is Redgate SQL Monitor. Red Gate recently released SQL Monitor 4.0, which is a bit of an upgrade from the old 3.x versions, which now includes advanced query tracing.
In a nutshell, what SQL Monitor does is connect to your SQL Server (it needs its own database for stats and such, as well as having a sysadmin account for reading database stats) and it just reports all sorts of statics on your SQL Server – disk and processor queues, RAM usage, database hits, etc… All of this is divided in two main areas – Alerts and Analysis. It’s an application that provides a Web Server (runs on Windows) and has a nice little web GUI that is somewhat configurable.
Alerts provide all kinds of tunable alerts – ranging from job failures to deadlocks to long running queries to even things like table blocking. You can set up email alerting as well and get emailed these alerts as they happen. At first though, I would suggest not enabling the Email Alerts, especially if you have never monitored your server before, because this thing will send you all kinds emails about things it finds (log backups overdue, index fragmentation, etc…). As I mentioned, these alerts are tunable, which is really handy. We have one job that runs in a loop for 8 hours at a time – when we first started monitoring, SQL Monitor would constantly send us emails about a long running query, since, let’s face it, 8 hours is a long running query. You can tune that though and basically tell the alert that “Hey, it’s okay if it runs for 8 hours, but if it deviates from that by more than say… 5%, then send us an alert”. These alerts at first look annoying, but once you sift through them, there is a lot very very useful data.
An example of that is that we have been having about 2 or 3 deadlocks per day. For the longest time, we weren’t sure what was causing them – we could view the first part of the query, but it was so generic that it didn’t provide enough insight in to what was causing the deadlock to really give us any meaningful information. SQL Monitor was able to give us the whole query, the job / stored proc that was causing the deadlock and that, in turn, let us figure out how to fix it (a few indexes here and there fixed that issue).
Analysis also is absolutely invaluable – I spend about 75% of my time doing systems and storage administration, and a big part of that is performance tuning / getting the best possible performance out of our equipment and servers. This includes doing things like query and SQL Job tuning. We use things like LogicMonitor that provides WMI Monitoring of the physical hardware, Nagios / Opsview that provides us with alerts on hardware usage, but nothing else really to give us specifics (and by that I mean deep specifics such as stats over time, cache hits, database size, log size, full scans, etc…) about SQL Server. SQL Monitor fills that gap really nicely. It provides in depth analysis on a daily, weekly, monthly and even yearly basis (the SQLMonitor database gets quite big if you keep data for that long however, we keep SQLMonitor data for 2 months and the database sits around 30 gigs).
I suppose one of the other awesome things I should mention is that it is totally cluster aware. Our previous installation was a 3 node cluster, our current is a 2 node cluster. It monitors both nodes, but of course only monitors the SQL Server instance on the server that owns the SQL Server resources (disks, etc…). They have a free trail available (14 days), so you can give it a shot.
The price tag is somewhat hefty, but if you are running a production database that either your internal users use or you have a lot of external uses, the data and stats it provides are invaluable compared to other tools.