I’ve been working on a simple monitoring tool for SQL Server over the last few months. I often find myself asking “What just happened?” and “Was it SQL Server?”. I wanted a small utility I could leave running at clients that would answer that question. It needed to be simple enough that a non-SQL Server person could look at it and get an idea if there was an issue with SQL Server. But also sophisticated enough that it would point me in the right direction.
It will capture the following information for each server every minute:
- CPU Usage for SQL Server and non-SQL Server tasks
- Batch requests per second
- Disk Read and Write volume and Page Life Expectancy
- The largest waits grouped together. For example, the PAGEIO* waits are all grouped together as Disk IO waits. You can customize this mapping for your environment.
- It also captures some basic information about the server including the version, restart date and aggregate database size.
When you bring up a server page it will show the currently running queries and the last 60 minutes of the metrics above. When someone reports an issue I can look at the last hour for any server with just two clicks. And quickly answer: “Is It SQL Server?”
I also wanted this to be very simple to use and install. It can run as a simple command line executable. It doesn’t require .NET or Java or even SQL Server. The only requirement is some flavor of ODBC driver for SQL Server which nearly every server has. It hosts a self-contained web server that displays the information you’re looking for. It includes an option to install itself as a service so it runs in the background and is always available. Adding servers is as simple as putting their names into a text file.
All the big servers I monitor already have a fancy monitoring solution. But I’ve never been happy with the solutions for the second tier of servers or the smaller clients that won’t buy a dedicated monitoring solution. So this is what I’ve been using.
You can download this at http://www.scalesql.com/downloads/isitsql.1.0.17.zip. There’s a README document that will help you get started.