SQL Monitor’s data repository

As one of the developers of SQL Monitor, I often get requests passed on by our support people from customers who are looking to dip into SQL Monitor’s own data repository, in order to pull out bits of information that they’re interested in. Since there’s clearly interest out there in playing around directly with the data repository, I thought I’d write some blog posts to start to describe how it all works. The hardest part for me is knowing where to begin, since the schema of the data repository is pretty big. Hmmm… I guess it’s tricky for anyone to write anything but the most trivial of queries against the data repository without understanding the hierarchy of monitored objects, so perhaps my first post should start there.

I always imagine that whenever a customer fires up SSMS and starts to explore their SQL Monitor data repository database, they become immediately bewildered by the schema – that was certainly my experience when I did so for the first time. The following query shows the number of different object types in the data repository schema:

  type_desc count
12 VIEW 124

With 193 tables, 124 views, 100 stored procedures and 381 table valued functions, that’s quite a hefty schema, and when you browse through it using SSMS, it can be a bit daunting at first. So, where to begin? Well, let’s narrow things down a bit and only look at the tables belonging to the data schema. That’s where all of the collected monitoring data is stored by SQL Monitor. The following query gives us the names of those tables:

This query still returns 110 tables. I won’t show them all here, but let’s have a look at the first few of them:

1 data.Cluster_Keys
2 data.Cluster_Machine_ClockSkew_UnstableSamples
3 data.Cluster_Machine_Cluster_StableSamples
4 data.Cluster_Machine_Keys
5 data.Cluster_Machine_LogicalDisk_Capacity_StableSamples
6 data.Cluster_Machine_LogicalDisk_Keys
7 data.Cluster_Machine_LogicalDisk_Sightings
8 data.Cluster_Machine_LogicalDisk_UnstableSamples
9 data.Cluster_Machine_LogicalDisk_Volume_StableSamples
10 data.Cluster_Machine_Memory_Capacity_StableSamples
11 data.Cluster_Machine_Memory_UnstableSamples
12 data.Cluster_Machine_Network_Capacity_StableSamples
13 data.Cluster_Machine_Network_Keys
14 data.Cluster_Machine_Network_Sightings
15 data.Cluster_Machine_Network_UnstableSamples
16 data.Cluster_Machine_OperatingSystem_StableSamples
17 data.Cluster_Machine_Ping_UnstableSamples
18 data.Cluster_Machine_Process_Instances
19 data.Cluster_Machine_Process_Keys
20 data.Cluster_Machine_Process_Owner_Instances
21 data.Cluster_Machine_Process_Sightings
22 data.Cluster_Machine_Process_UnstableSamples

There are two things I want to draw your attention to:

  1. The table names describe a hierarchy of the different types of object that are monitored by SQL Monitor (e.g. clusters, machines and disks).
  2. For each object type in the hierarchy, there are multiple tables, ending in the suffixes _Keys, _Sightings, _StableSamples and _UnstableSamples.

Not every object type has a table for every suffix, but the _Keys suffix is especially important and a _Keys table does indeed exist for every object type. In fact, if we limit the query to return only those tables ending in _Keys, we reveal the full object hierarchy:

1 data.Cluster_Keys
2 data.Cluster_Machine_Keys
3 data.Cluster_Machine_LogicalDisk_Keys
4 data.Cluster_Machine_Network_Keys
5 data.Cluster_Machine_Process_Keys
6 data.Cluster_Machine_Services_Keys
7 data.Cluster_ResourceGroup_Keys
8 data.Cluster_ResourceGroup_Resource_Keys
9 data.Cluster_SqlServer_Agent_Job_History_Keys
10 data.Cluster_SqlServer_Agent_Job_Keys
11 data.Cluster_SqlServer_Database_BackupType_Backup_Keys
12 data.Cluster_SqlServer_Database_BackupType_Keys
13 data.Cluster_SqlServer_Database_CustomMetric_Keys
14 data.Cluster_SqlServer_Database_File_Keys
15 data.Cluster_SqlServer_Database_Keys
16 data.Cluster_SqlServer_Database_Table_Index_Keys
17 data.Cluster_SqlServer_Database_Table_Keys
18 data.Cluster_SqlServer_Error_Keys
19 data.Cluster_SqlServer_Keys
20 data.Cluster_SqlServer_Services_Keys
21 data.Cluster_SqlServer_SqlProcess_Keys
22 data.Cluster_SqlServer_TopQueries_Keys
23 data.Cluster_SqlServer_Trace_Keys
24 data.Group_Keys

The full object type hierarchy looks like this:

  • Cluster
    • Machine
      • LogicalDisk
      • Network
      • Process
      • Services
    • ResourceGroup
      • Resource
    • SqlServer
      • Agent
        • Job
          • History
      • Database
        • BackupType
          • Backup
        • CustomMetric
        • File
        • Table
          • Index
      • Error
      • Services
      • SqlProcess
      • TopQueries
      • Trace
  • Group

Okay, but what about the individual objects themselves represented at each level in this hierarchy? Well that’s what the _Keys tables are for. This is probably best illustrated by way of a simple example – how can I query my own data repository to find the databases on my own PC for which monitoring data has been collected? Like this:

  cluster_name instance_name database_name
1 dev-chrisl2 SqlMonitorData
2 dev-chrisl2 master
3 dev-chrisl2 model
4 dev-chrisl2 msdb
5 dev-chrisl2 mssqlsystemresource
6 dev-chrisl2 tempdb
7 dev-chrisl2 sql2005 SqlMonitorData
8 dev-chrisl2 sql2005 TestDatabase
9 dev-chrisl2 sql2005 master
10 dev-chrisl2 sql2005 model
11 dev-chrisl2 sql2005 msdb
12 dev-chrisl2 sql2005 mssqlsystemresource
13 dev-chrisl2 sql2005 tempdb
14 dev-chrisl2 sql2008 SqlMonitorData
15 dev-chrisl2 sql2008 master
16 dev-chrisl2 sql2008 model
17 dev-chrisl2 sql2008 msdb
18 dev-chrisl2 sql2008 mssqlsystemresource
19 dev-chrisl2 sql2008 tempdb

These results show that I have three SQL Server instances on my machine (a default instance, one named sql2005 and one named sql2008), and each instance has the usual set of system databases, along with a database named SqlMonitorData. Basically, this is where I test SQL Monitor on different versions of SQL Server, when I’m developing. There are a few important things we can learn from this query:

  1. Each _Keys table has a column named Id. This is the primary key.
  2. Each _Keys table has a column named ParentId. A foreign key relationship is defined between each _Keys table and its parent _Keys table in the hierarchy. There are two exceptions to this, Cluster_Keys and Group_Keys, because clusters and groups live at the root level of the object hierarchy.
  3. Each _Keys table has a column named _Name. This is used to uniquely identify objects in the table within the scope of the same shared parent object.

Actually, that last item isn’t always true. In some cases, the _Name column is actually called something else. For example, the data.Cluster_Machine_Services_Keys table has a column named _ServiceName instead of _Name (sorry for the inconsistency). In other cases, a name isn’t sufficient to uniquely identify an object. For example, right now my PC has multiple processes running, all sharing the same name, Chrome (one for each tab open in my web-browser). In such cases, multiple columns are used to uniquely identify an object within the scope of the same shared parent object.

Well, that’s it for now. I’ve given you enough information for you to explore the _Keys tables to see how objects are stored in your own data repositories. In a future post, I’ll try to explain how monitoring data is stored for each object, using the _StableSamples and _UnstableSamples tables. If you have any questions about this post, or suggestions for future posts, just submit them in the comments section below.