sqlskills-logo-2015-white.png

How to Do Some Very Basic SQL Server Monitoring

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!

13 thoughts on “How to Do Some Very Basic SQL Server Monitoring

  1. Hi, if you are willing to do some basic and advanced monitoring with no cost, you could also consider using “dbWarden”. I’ve been using it for almost 1,5 year and it’s quite good.

    There is a health report that you can get every day and in which there is plenty of useful information.

  2. Great thing for a quick start.. i think some more information collection should be added like blocking, wait types etc..

  3. Hello,

    Nice article. I tried to download from the link, but it says file not found. Can I get the ServerMonitor Database.zip? Can you share me the ftp location for the download?

    Thanks in advance,

    Elango C

    1. It is working for me, and I have not had any other complaints. The file is hosted on DropBox, so maybe that is blocked for you. I will send the zip file to your e-mail address.

  4. Hi Glenn,

    I could see more that 100 value in SQLServerCPUUtilization column. But during the same time server CPU is less than 100 percent as per Task Manager. I am running this monitoring process on SQL Server 2014. Do i need to do any changes. Please suggest.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.