What does it mean for my environment if I find that the spt_monitor table in SQL Server is empty?

What is this table, how is it used and what populates it?

I am using SQL Server 2005.

enter image description here

My spt_monitor - table is not empty.

I just asked if it were empty....?

  • 1
    It's an undocumented system table so there's no information about whether it should or should not have a row in it. But sp_monitor is the documented way to get information from that table; what results does it return? And what version of SQL Server do you have? – Pondlife Dec 14 '12 at 12:39
  • Why are you concerned that this table is empty? Are you trying to gather information or is there some problem you're trying to troubleshoot? That data can be retrieved in other ways. – Mike Fal Dec 14 '12 at 18:49

To answer your specific question: "What would it tell me if this table were empty?" - It would tell you that you or some process somehow truncated or deleted this table. It should always have a row, and when you run the sp_monitor system stored procedure this row gets overwritten with a new row.

More Information on spt_monitor and sp_monitor

spt_monitor is a system table stored in the master database. This table gets populated for the first time when you instal SQL Server. It contains various information about some system resources utliization that are tracked inside of SQL Server (described below).

The purpose of this table is to provide a way for you to compare some of these metrics over time. The table will only ever have one row. The work of giving the differences is done in the stored procedure described below -

sp_monitor is a system stored procedure stored procedure in the master database. This stored procedure reads the current row in spt_monitor into a temporary table and then inserts a new row and compares the difference between all of the various counters tracked (including the lastrun column to give an elapsed time.

This stored procedure then gives the current value for each counter as well as the difference in the time between executes in parenthesis.

I've included a quick sample output from my one of my virtual machines. This is activity on a mostly sleeping VM for a 46 second timeframe. To explain the counters, look at the packets_received column. There have been a total of 777 packets received on this instance since it was last restarted according to the @@pack_received system function, and in the past 46 seconds there has been on received.

Sample Output:

last_run                current_run             seconds
----------------------- ----------------------- -----------
2012-12-15 22:39:56.770 2012-12-15 22:40:42.500 46

cpu_busy                  io_busy                   idle
------------------------- ------------------------- -------------------------
5(0)-0%                   3(0)-0%                   12366(44)-95%

packets_received         packets_sent             packet_errors
------------------------ ------------------------ ------------------------
777(1)                   627(1)                   0(0)

total_read          total_write         total_errors        connections
------------------- ------------------- ------------------- ------------------
723(0)              153(1)              0(0)                275(0)

What Do You Do if You Find The Table Empty?

Again, I am pretty sure that an empty table here is an indication that you, or some monitoring tool or process you are running emptied the table. Or possibly that there was an issue with a portion of the installation. If you find this table empty, there should be a file in your SQLRoot\MSSQL\Install directory called u_tables.sql This file creates some system tables and populates them. If you search for spt_monitor you'll see the create and insert script for this table. The reelinsert script (in SQL Server 2012 RTM) looks like this:

insert into spt_monitor
    lastrun = getdate(),
    cpu_busy = @@cpu_busy,
    io_busy = @@io_busy,
    idle = @@idle,
    pack_received = @@pack_received,
    pack_sent = @@pack_sent,
    connections = @@connections,
    pack_errors = @@packet_errors,
    total_read = @@total_read,
    total_write = @@total_write,
    total_errors = @@total_errors

A Final Word

I'm not sure why you are using this table. I would suggest that you read up on each of the system functions above in books online. Some of them have notes of warning about various conditions where they can return incorrect data. For instance the note for [@@cpu_busy][1] talks about the data type and a warning that if it accumulates more than 49 days of time, it is no longer accurate or valid. Same with some of the other counters.

I have actually never run sp_monitor on an instance of mine. It wasn't until this question that I dug deeper and learned about what it is and what it does. It looks interesting, it provides data on a host of counters (you can learn about each of those functions above here) that look to provide some useful data at first blush.

But then when you look deeper, most of the information grabbed is probably best collected in other ways. For most of the information, I would look at a combination of perfmon counters, DMVs and virtual file stats. The total_errors counter is interesting but I can't really think of a useful case for the information presented that way. I want to get my disk write errors (total_errors) and alerts as they happen and not worry about a cumulative count so much. Looking at total connections since a restart could be interesting and if you didn't want to track this information through perfmon or repeated queries at DMVs and adding it, that could be helpful.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy