One thing that I think is very important for a database professional to do as part of their regular work is to have some idea of how their most important SQL Server instances are running and how they have been performing over time. This helps you get a feel for what a normal workload is for your instance and what your workload extremes look like. It also helps 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 this purpose such as SQL Sentry for SQL Server from SentryOne and SQL Monitor from Redgate. Regardless of whether you use any of those products, I think you should also consider using something that is very simple, lightweight and free, such as my ServerMonitor database and related SQL Server Agent job, which you can download from here. This version 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, 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 for that table if you have SQL Server 2008 Enterprise Edition or newer. Finally, it creates a SQL Server Agent job called “Record Instance Level Metrics” that runs once every minute, to 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.

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 just has a few example queries for pulling some useful information out of the ServerMonitor database. The ServerMonitor database is just a simple example that anyone can understand and easily extend if they want to. Please let me know what you think. Thank you!