SQL Monitor’s data repository
- by Chris Lambrou
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;
type_desccount
1DEFAULT_CONSTRAINT63
2FOREIGN_KEY_CONSTRAINT181
3INTERNAL_TABLE3
4PRIMARY_KEY_CONSTRAINT190
5SERVICE_QUEUE3
6SQL_INLINE_TABLE_VALUED_FUNCTION381
7SQL_SCALAR_FUNCTION2
8SQL_STORED_PROCEDURE100
9SYSTEM_TABLE41
10UNIQUE_CONSTRAINT54
11USER_TABLE193
12VIEW124
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:
name
1data.Cluster_Keys
2data.Cluster_Machine_ClockSkew_UnstableSamples
3data.Cluster_Machine_Cluster_StableSamples
4data.Cluster_Machine_Keys
5data.Cluster_Machine_LogicalDisk_Capacity_StableSamples
6data.Cluster_Machine_LogicalDisk_Keys
7data.Cluster_Machine_LogicalDisk_Sightings
8data.Cluster_Machine_LogicalDisk_UnstableSamples
9data.Cluster_Machine_LogicalDisk_Volume_StableSamples
10data.Cluster_Machine_Memory_Capacity_StableSamples
11data.Cluster_Machine_Memory_UnstableSamples
12data.Cluster_Machine_Network_Capacity_StableSamples
13data.Cluster_Machine_Network_Keys
14data.Cluster_Machine_Network_Sightings
15data.Cluster_Machine_Network_UnstableSamples
16data.Cluster_Machine_OperatingSystem_StableSamples
17data.Cluster_Machine_Ping_UnstableSamples
18data.Cluster_Machine_Process_Instances
19data.Cluster_Machine_Process_Keys
20data.Cluster_Machine_Process_Owner_Instances
21data.Cluster_Machine_Process_Sightings
22data.Cluster_Machine_Process_UnstableSamples
23…
There are two things I want to draw your attention to:
The table names describe a hierarchy of the different types of object that are monitored by SQL Monitor (e.g. clusters, machines and disks).
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;
name
1data.Cluster_Keys
2data.Cluster_Machine_Keys
3data.Cluster_Machine_LogicalDisk_Keys
4data.Cluster_Machine_Network_Keys
5data.Cluster_Machine_Process_Keys
6data.Cluster_Machine_Services_Keys
7data.Cluster_ResourceGroup_Keys
8data.Cluster_ResourceGroup_Resource_Keys
9data.Cluster_SqlServer_Agent_Job_History_Keys
10data.Cluster_SqlServer_Agent_Job_Keys
11data.Cluster_SqlServer_Database_BackupType_Backup_Keys
12data.Cluster_SqlServer_Database_BackupType_Keys
13data.Cluster_SqlServer_Database_CustomMetric_Keys
14data.Cluster_SqlServer_Database_File_Keys
15data.Cluster_SqlServer_Database_Keys
16data.Cluster_SqlServer_Database_Table_Index_Keys
17data.Cluster_SqlServer_Database_Table_Keys
18data.Cluster_SqlServer_Error_Keys
19data.Cluster_SqlServer_Keys
20data.Cluster_SqlServer_Services_Keys
21data.Cluster_SqlServer_SqlProcess_Keys
22data.Cluster_SqlServer_TopQueries_Keys
23data.Cluster_SqlServer_Trace_Keys
24data.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:
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;
cluster_nameinstance_namedatabase_name
1dev-chrisl2SqlMonitorData
2dev-chrisl2master
3dev-chrisl2model
4dev-chrisl2msdb
5dev-chrisl2mssqlsystemresource
6dev-chrisl2tempdb
7dev-chrisl2sql2005SqlMonitorData
8dev-chrisl2sql2005TestDatabase
9dev-chrisl2sql2005master
10dev-chrisl2sql2005model
11dev-chrisl2sql2005msdb
12dev-chrisl2sql2005mssqlsystemresource
13dev-chrisl2sql2005tempdb
14dev-chrisl2sql2008SqlMonitorData
15dev-chrisl2sql2008master
16dev-chrisl2sql2008model
17dev-chrisl2sql2008msdb
18dev-chrisl2sql2008mssqlsystemresource
19dev-chrisl2sql2008tempdb
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:
Each _Keys table has a column named Id. This is the primary key.
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.
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.
