Okay, I had originally intended to blog about SQL Monitor on a fairly frequent basis. Unfortunately I got rather distracted towards the end of last year by a whole host of things, both personal and work related, and I ended up only writing two posts. That was a bit feeble, so now I’m going to try make a much more concerted effort to find time to post more frequently.

In my first post I introduced the SQL Monitor data repository, and described how the monitored objects are stored in a hierarchy in the data schema, in a series of tables with a _Keys suffix. In my second post I introduced how SQL Monitor stores alerts. This was by special request from a SQL Monitor user, and was a sidetrack from my original intention to elaborate further on how monitoring data is stored, which is what this post is about.

I’ve previously described the hierarchy of the different types of monitored object, and how instances of each type of monitored object are stored in a table with a _Keys suffix. The actual monitoring data for each monitored object can be found in tables alongside the _Keys table, in similarly named tables with a _StableSamples or _UnstableSamples suffix. For example, let’s have a look at the tables used to store information about a monitored server’s logical disks:

Let’s quickly recap how logical disk objects are stored in the _Keys tables. The entries of the data.Cluster_Machine_LogicalDisk_Keys table on my laptop are as follows:

This indicates that I have three logical disks on my laptop, C:, D: and HarddiskVolume1 (this is a hidden partition that’s not mounted, so it doesn’t get a drive letter). The ParentId references an entry in the data.Cluster_Machine_Keys table, which contains the following:

This represents a machine with no name, whose ParentId in turn references an entry in data.Cluster_Keys, which contains the following:

This shows two detected clusters, granger (my laptop) and granger-vm (a virtual machine I happen to be running). All of the above can be captured in a single query, to show all logical disks across all monitored servers.

Okay, as you can probably see, I’m not monitoring very much. I have a development version of SQL Monitor on my laptop that’s only monitoring itself. If you run this query on your own data repository, however, you’ll likely see far more entries.

So where is the actual raw monitoring data stored for these logical disks? It’s in data.Cluster_Machine_LogicalDisk_UnstableSamples. As you can probably imagine, this table can contain a large number of rows, since disk monitoring data is captured every 15 seconds, and you may have months of data for many more logical disks. Here’s what I’ve collected within the last three minutes.

The values highlighted in red and blue are mentioned further below.

Well, what does all this mean? Let’s look at each of the columns in turn.

• Id – This is the Id of the logical disk from the data.Cluster_Machine_LogicalDisk_Keys (or LogicalDiskId in my query above).
• CollectionDate – The time at which the monitoring data was collected.
• _CumulativeIdleTime – The cumulative time that this disk has spent idling.
• _CumulativeReadBytes – The cumulative number of bytes read by this disk.
• _CumulativeReads – The cumulative number of read operations performed by this disk.
• _CumulativeReadTime – The cumulative time spent by this disk on read operations.
• _CumulativeWriteBytes – The cumulative number of bytes written by this disk.
• _CumulativeWrites – The cumulative number of write operations performed by this disk.
• _CumulativeWriteTime – The cumulative time spent by this disk on write operations.
• _FreeBytes – The number of free bytes remaining on this disk.

Hmm, there are a few questions that these results raise, which I need to cover in a little detail.

1. Why aren’t the CollectionDate values datetimes?
2. What are the units of the time-based properties _CumulativeIdleTime, _CumulativeReadTime and _CumulativeWriteTime?
3. Most of the properties are cumulative. How do I work with them?
4. Is there an easier way to tie the raw data back to the cluster, machine and logical disk?

Why aren’t the CollectionDate values datetimes?

The CollectionDate column represents the timestamp for each data collection, but the type is bigint not datetime. This is because the native datetime SQL type can only represent timestamps to a precision of approximately 3.3 milliseconds. For various reasons, including a desire to retain the original sampling accuracy of the raw data, this isn’t sufficiently precise. There is a native datetime2 SQL type which is much more precise, but this was only introduced in SQL Server 2008 and the monitoring code in SQL Monitor predates this. Therefore, to retain the original sampling precision, timestamps are stored by SQL Monitor as bigints with a precision of 100 nanoseconds (in fact, each timestamp value is represented by the number of complete 100 nanosecond intervals that have elapsed since midnight of January 1st in the year 0001 of the Gregorian calendar).

The SQL Monitor data repository includes two scalar functions that can be used to convert between the bigint timestamp representation and a datetime – utils.DateTimeToTicks and utils.TicksToDateTime. You can see an example of utils.DateTimeToTicks in action in the WHERE clause of the above query, where it’s used to limit the results to the most recent 3 minutes. For performance reasons, it is strongly recommended that you use the raw bigint values in any queries for filtering and ordering purposes. For example, in the above query, the WHERE clause

could have been written as

but this would make the query significantly more expensive for SQL Server to evaluate as it can no longer take advantage of any indexes based on the CollectionDate.

What are the units of the time-based properties _CumulativeIdleTime, _CumulativeReadTime and _CumulativeWriteTime?

For the properties _CumulativeReadBytes, _CumulativeWriteBytes and _FreeBytes, it’s fairly obvious that the values represent a number of bytes. Likewise, _CumulativeReads and _CumulativeWrites represent the number of read and write operations that a disk has performed, which are simple unitless counts. So what are the units of the _CumulativeIdleTime, _CumulativeReadTime and _CumulativeWriteTime properties? Well, they represent times but their units aren’t seconds. They’re hundreds of nano-seconds (1 × 10-7 seconds). If you want to convert these values to seconds, you need to divide by 10,000,000. In fact, this is the same unit as for CollectionDate.

Most of the properties are cumulative. How do I work with them?

With the exception of _FreeBytes, all data values are cumulative. For example, if I want to know how much time my C: drive spent idling for the most recent minute, I need to look at the samples at the start and end of that minute, i.e. rows 20 and 24 in the result set above (highlighted in red and blue). The time spent idle, according to the _CumulativeIdleTime values, is given by
(953,056,054,000 – 952,527,408,000) / 10,000,000 = 52.865 seconds
The duration of that interval, according to the CollectionDate values, is given by
(634,938,047,025,651,221 – 634,938,046,425,456,892) / 10,000,000 = 60.019 seconds
The proportion of time spent idle over that minute is thus given by
52.865 / 60.019 = 0.8808 = 88%

There are a number of different strategies for performing this calculation between pairs of samples for an entire series of samples. Here’s one that involves selecting the records of interest into a temporary table, and then performing a second query against the temporary table, that joins together adjacent pairs of records.

Notice that the calculation for most of the properties involves a multiplication factor, either to convert from “per 100 nanoseconds” to “per seconds”, or to convert a bare ratio to a percentage. It’s also important that the multiplication is expressed as a float value rather than a bigint. This avoids rounding errors that otherwise would have occurred with bigint division.

There’s another calculation that’s also very useful, which is where we need to divide two cumulative changes. One classic example of this is for the Disk avg. read time and Disk avg. write time properties. For any given time interval, Disk avg. read time is calculated by dividing the total time spent reading by the number of read operations carried out. Likewise, Disk avg. write time is calculated by dividing the total time spent writing by the number of write operations carried out. These two properties can be obtained by modifying the SELECT query against the temporary table used above:

Unfortunately this query fails with a “Divide by zero error encountered” error message whenever any interval is encountered in which no read or write operations occur. This can be resolved by adjusting the calculations to cope with a possible divide by zero scenario.

Is there an easier way to tie the raw data back to the cluster, machine and logical disk?

In the queries above, we could further join from the _UnstableSamples tables back to the different _Keys tables and also convert the bigint timestamps to datetimes, to provide more human readable output like this:

Well, for each _UnstableSamples table, there is a corresponding view suffixed with _UnstableSamples_View that already performs these joins and date conversions for you. The above query can be rewritten as follows, whilst producing exactly the same output albeit with slightly different column names.

Some more examples

Okay, I’ve covered all I wanted to for this post. I haven’t yet explored the _StableSamples or _Instances tables, but most of the interesting stuff is in the _UnstableSamples tables I’ve already described. Now it’s time for a few more examples.

CPU performance monitoring data

The raw data from which the CPU performance metrics in SQL monitor are all derived can be found in the monitoringCluster_Machine_Processors_UnstableSamples table, but it’s easier to examine the corresponding view, in this case to see what’s been going on in the most recent 3 minutes.

All of these raw values correspond to the raw Windows Performance Counters provided by Windows. Just like the time-based properties we’ve explored in Cluster_Machine_LogicalDisk_UnstableSamples, most of the properties in Cluster_Machine_Processors_UnstableSamples are cumulative time samples, based on 100 nanosecond units.

SQL Monitor actually only displays two metrics derived from these values:

1. Avg. CPU queue length – This corresponds directly to the Cluster_Machine_Processors_AverageQueueLength column in the above query.
2. Machine: processor time – This is trivially derived from the Cluster_Machine_Processors_CumulativeIdleTime column in the above query (i.e. % cpu usage = 100 – % idle time).

SQL Server statistics

The Cluster_SqlServer_SqlStatistics_UnstableSamples table contains the raw monitoring data for the Batch requests/sec, Compilations/sec and Compilations/batch metrics displayed in SQL Monitor’s Analysis tab. Let’s have a look at the corresponding view, to see what’s been going on in the most recent 3 minutes.

The SQL Monitor metrics are derived from this table as follows:

• Batch requests/sec is based on the increase in Cluster_SqlServer_SqlStatistics_CumulativeBatchRequests between successive samples.
• Compilations/sec is based on the increase in Cluster_SqlServer_SqlStatistics_CumulativeCompilations between successive samples.
• Compilations/batch is based on the increase in Cluster_SqlServer_SqlStatistics_CumulativeCompilations divided by the increase in Cluster_SqlServer_SqlStatistics_CumulativeBatchRequests between successive samples.

If you want to calculate Compilations/batch in your own queries, don’t forget to handle the possible “divide by zero” error in the case where there are no cumulative batch requests within any time interval your query is concerned with.

Custom metrics

Retrieving the raw monitoring data for custom metrics is a little different to all of the other metrics, mainly because Cluster_SqlServer_Database_CustomMetric_UnstableSamples_View only contains a Cluster_SqlServer_Database_CustomMetric_MetricId property, not the actual custom metric name. You can still make use of this view, but a single join to another table is required in order to include the name of the custom metric in the results. Here are all of the custom metrics that have been running on my laptop for the past 3 minutes.

The interpretation of the raw custom metric values obviously vary from metric to metric. For any metrics imported from Red Gate’s own SQL Monitor Metrics web-site, you should consult the individual metric descriptions. For your own metrics, I hope you already know how they work!

Right, that’s it for this post. I hope you’re inspired to investigate some of the other _UnstableSamples tables to see what other raw data is available. If you have any questions, please either leave a comment or email me directly (chris.lambrou@red-gate.com) and I’ll see what I can do to help.