Basic SQL Server Monitoring

Introduction

One important task for a database professional is to have a good idea of how their SQL Server instances are currently running. You should also know how they have been performing over time. I would call this basic SQL Server monitoring. Knowing your basic baseline metrics helps you get a feel for what a normal workload is for your instance. This also helps you understand what your workload extremes look like. It also lets you answer the inevitable questions about “What happened to the database server last Thursday at 2PM”?

There are many fine 3rd party products available for much more detailed monitoring such as SQL Sentry for SQL Server from SentryOne. Smaller organizations with limited budgets can also leverage products like SQL Sentry Essentials.

Basic SQL Server Monitoring

Regardless of whether you use a 3rd Party product or not, I think you should also consider using something that is very simple, lightweight and free. For example, my ServerMonitor database and related SQL Server Agent job, which you can download from here. ServerMonitor only works on SQL Server 2008 or newer.

The zip file includes two separate T-SQL scripts. The first script creates a database called ServerMonitor in the default database location on your instance. It sets the recovery model to Simple, and then creates one table and two stored procedures in that database. It also uses PAGE data compression on the indexes if possible. Finally, it creates a SQL Server Agent job called “Record Instance Level Metrics” that runs once every minute. This will collect a few instance-level metrics and store them in the ServerMonitor database. You can easily modify the schedule for that job if you wish.

ServerMonitor Metrics

These basic metrics include: Average Task Count, Average Runnable Task Count, Average Pending IO Count, SQL Server process CPU utilization, and Average Page Life Expectancy across all NUMA nodes. This is just some very basic, easy to collect information that can be quite useful for getting some baseline and trending information about your instance over time.

The second script has a few example queries for pulling some useful information out of the ServerMonitor database. The ServerMonitor database is just a simple example that is very easy to understand. This database can also be a good place to store other useful utility queries.

I have some additional scripts on my Resources page that are very useful for monitoring SQL Server instances. Please let me know what you think. Thank you!