Sql-server – What does @RC mean in SSDT SQL Server Unit Testing

We've been doing this for almost five years, and we think that explicitly testing modifications is definitely doable, but it is quite slow. Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

I've written an article entitled Close Those Loopholes: Lessons learned from Unit Testing T-SQL, as well as some blog posts

Regarding your question "Is there a treshold in complexity where it gets completely hopeless?", complex modules need tests much more than simple ones.

To simplify maintenance, we generate expected results, and we store them in separate files - that makes a huge difference.

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:

sp_monitor
------------------------*/
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
    select
    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
go

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.