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:

SELECT type_desc,
    COUNT(*) AS [count]
  FROM sys.objects
  GROUP BY type_desc
  ORDER BY type_desc;

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:

SELECT sch.name + '.' + obj.name AS [name]
  FROM sys.objects obj 
  JOIN 
    sys.schemas sch
  ON
    sch.schema_id = obj.schema_id
  WHERE obj.type_desc = 'USER_TABLE'
    AND sch.name = 'data'
  ORDER BY sch.name,
    obj.name;

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:

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:

SELECT sch.name + '.' + obj.name AS [name]
  FROM sys.objects obj 
  JOIN 
    sys.schemas sch
  ON
    sch.schema_id = obj.schema_id
  WHERE obj.type_desc = 'USER_TABLE'
    AND sch.name = 'data'
    AND obj.name LIKE '%_Keys'
  ORDER BY sch.name,
    obj.name;

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:

SELECT clstr._Name AS cluster_name,
    srvr._Name AS instance_name,
    db._Name AS database_name
  FROM data.Cluster_SqlServer_Database_Keys db
  JOIN 
    data.Cluster_SqlServer_Keys srvr
  ON
    db.ParentId = srvr.Id  -- Note here how the parent of a Database is a Server
  JOIN 
    data.Cluster_Keys clstr
  ON
    srvr.ParentId = clstr.Id -- Note here how the parent of a Server is a Cluster
  WHERE clstr._Name = 'dev-chrisl2' -- This is the hostname of my own PC
  ORDER BY clstr._Name,
    srvr._Name,
    db._Name;

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.