{"id":4160,"date":"2012-08-23T23:40:33","date_gmt":"2012-08-23T23:40:33","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-monitor-data-repository\/"},"modified":"2016-07-28T10:51:30","modified_gmt":"2016-07-28T10:51:30","slug":"sql-monitor-data-repository","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-monitor-data-repository\/","title":{"rendered":"SQL Monitor’s data repository"},"content":{"rendered":"

As one of the developers of SQL Monitor<\/a>, 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.<\/p>\n

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:<\/p>\n

SELECT type_desc,\n    COUNT(*) AS [count]\n  FROM sys.objects\n  GROUP BY type_desc\n  ORDER BY type_desc;<\/pre>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
 <\/th>\ntype_desc<\/th>\ncount<\/th>\n<\/tr>\n
1<\/th>\nDEFAULT_CONSTRAINT<\/td>\n63<\/td>\n<\/tr>\n
2<\/th>\nFOREIGN_KEY_CONSTRAINT<\/td>\n181<\/td>\n<\/tr>\n
3<\/th>\nINTERNAL_TABLE<\/td>\n3<\/td>\n<\/tr>\n
4<\/th>\nPRIMARY_KEY_CONSTRAINT<\/td>\n190<\/td>\n<\/tr>\n
5<\/th>\nSERVICE_QUEUE<\/td>\n3<\/td>\n<\/tr>\n
6<\/th>\nSQL_INLINE_TABLE_VALUED_FUNCTION<\/td>\n381<\/td>\n<\/tr>\n
7<\/th>\nSQL_SCALAR_FUNCTION<\/td>\n2<\/td>\n<\/tr>\n
8<\/th>\nSQL_STORED_PROCEDURE<\/td>\n100<\/td>\n<\/tr>\n
9<\/th>\nSYSTEM_TABLE<\/td>\n41<\/td>\n<\/tr>\n
10<\/th>\nUNIQUE_CONSTRAINT<\/td>\n54<\/td>\n<\/tr>\n
11<\/th>\nUSER_TABLE<\/td>\n193<\/td>\n<\/tr>\n
12<\/th>\nVIEW<\/td>\n124<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
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<\/em> 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:<\/p>\n
SELECT sch.name + '.' + obj.name AS [name]\n  FROM sys.objects obj \n  JOIN \n    sys.schemas sch\n  ON\n    sch.schema_id = obj.schema_id\n  WHERE obj.type_desc = 'USER_TABLE'\n    AND sch.name = 'data'\n  ORDER BY sch.name,\n    obj.name;<\/pre>\n
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:<\/p>\n<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
 <\/th>\nname<\/th>\n<\/tr>\n
1<\/th>\ndata.Cluster_Keys<\/td>\n<\/tr>\n
2<\/th>\ndata.Cluster_Machine_ClockSkew_UnstableSamples<\/td>\n<\/tr>\n
3<\/th>\ndata.Cluster_Machine_Cluster_StableSamples<\/td>\n<\/tr>\n
4<\/th>\ndata.Cluster_Machine_Keys<\/td>\n<\/tr>\n
5<\/th>\ndata.Cluster_Machine_LogicalDisk_Capacity_StableSamples<\/td>\n<\/tr>\n
6<\/th>\ndata.Cluster_Machine_LogicalDisk_Keys<\/td>\n<\/tr>\n
7<\/th>\ndata.Cluster_Machine_LogicalDisk_Sightings<\/td>\n<\/tr>\n
8<\/th>\ndata.Cluster_Machine_LogicalDisk_UnstableSamples<\/td>\n<\/tr>\n
9<\/th>\ndata.Cluster_Machine_LogicalDisk_Volume_StableSamples<\/td>\n<\/tr>\n
10<\/th>\ndata.Cluster_Machine_Memory_Capacity_StableSamples<\/td>\n<\/tr>\n
11<\/th>\ndata.Cluster_Machine_Memory_UnstableSamples<\/td>\n<\/tr>\n
12<\/th>\ndata.Cluster_Machine_Network_Capacity_StableSamples<\/td>\n<\/tr>\n
13<\/th>\ndata.Cluster_Machine_Network_Keys<\/td>\n<\/tr>\n
14<\/th>\ndata.Cluster_Machine_Network_Sightings<\/td>\n<\/tr>\n
15<\/th>\ndata.Cluster_Machine_Network_UnstableSamples<\/td>\n<\/tr>\n
16<\/th>\ndata.Cluster_Machine_OperatingSystem_StableSamples<\/td>\n<\/tr>\n
17<\/th>\ndata.Cluster_Machine_Ping_UnstableSamples<\/td>\n<\/tr>\n
18<\/th>\ndata.Cluster_Machine_Process_Instances<\/td>\n<\/tr>\n
19<\/th>\ndata.Cluster_Machine_Process_Keys<\/td>\n<\/tr>\n
20<\/th>\ndata.Cluster_Machine_Process_Owner_Instances<\/td>\n<\/tr>\n
21<\/th>\ndata.Cluster_Machine_Process_Sightings<\/td>\n<\/tr>\n
22<\/th>\ndata.Cluster_Machine_Process_UnstableSamples<\/td>\n<\/tr>\n
23<\/th>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
There are two things I want to draw your attention to:<\/p>\n
    \n
  1. The table names describe a hierarchy of the different types<\/em> of object that are monitored by SQL Monitor (e.g. clusters, machines and disks).<\/li>\n
  2. For each object type in the hierarchy, there are multiple tables, ending in the suffixes _Keys<\/em>, _Sightings<\/em>, _StableSamples<\/em> and _UnstableSamples<\/em>.<\/li>\n<\/ol>\n
    Not every object type has a table for every suffix, but the _Keys<\/em> suffix is especially important and a _Keys<\/em> table does indeed exist for every object type. In fact, if we limit the query to return only those tables ending in _Keys<\/em>, we reveal the full object hierarchy:<\/p>\n
    SELECT sch.name + '.' + obj.name AS [name]\n  FROM sys.objects obj \n  JOIN \n    sys.schemas sch\n  ON\n    sch.schema_id = obj.schema_id\n  WHERE obj.type_desc = 'USER_TABLE'\n    AND sch.name = 'data'\n    AND obj.name LIKE '%_Keys'\n  ORDER BY sch.name,\n    obj.name;<\/pre>\n
    \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
     <\/th>\nname<\/th>\n<\/tr>\n
    1<\/th>\ndata.Cluster_Keys<\/td>\n<\/tr>\n
    2<\/th>\ndata.Cluster_Machine_Keys<\/td>\n<\/tr>\n
    3<\/th>\ndata.Cluster_Machine_LogicalDisk_Keys<\/td>\n<\/tr>\n
    4<\/th>\ndata.Cluster_Machine_Network_Keys<\/td>\n<\/tr>\n
    5<\/th>\ndata.Cluster_Machine_Process_Keys<\/td>\n<\/tr>\n
    6<\/th>\ndata.Cluster_Machine_Services_Keys<\/td>\n<\/tr>\n
    7<\/th>\ndata.Cluster_ResourceGroup_Keys<\/td>\n<\/tr>\n
    8<\/th>\ndata.Cluster_ResourceGroup_Resource_Keys<\/td>\n<\/tr>\n
    9<\/th>\ndata.Cluster_SqlServer_Agent_Job_History_Keys<\/td>\n<\/tr>\n
    10<\/th>\ndata.Cluster_SqlServer_Agent_Job_Keys<\/td>\n<\/tr>\n
    11<\/th>\ndata.Cluster_SqlServer_Database_BackupType_Backup_Keys<\/td>\n<\/tr>\n
    12<\/th>\ndata.Cluster_SqlServer_Database_BackupType_Keys<\/td>\n<\/tr>\n
    13<\/th>\ndata.Cluster_SqlServer_Database_CustomMetric_Keys<\/td>\n<\/tr>\n
    14<\/th>\ndata.Cluster_SqlServer_Database_File_Keys<\/td>\n<\/tr>\n
    15<\/th>\ndata.Cluster_SqlServer_Database_Keys<\/td>\n<\/tr>\n
    16<\/th>\ndata.Cluster_SqlServer_Database_Table_Index_Keys<\/td>\n<\/tr>\n
    17<\/th>\ndata.Cluster_SqlServer_Database_Table_Keys<\/td>\n<\/tr>\n
    18<\/th>\ndata.Cluster_SqlServer_Error_Keys<\/td>\n<\/tr>\n
    19<\/th>\ndata.Cluster_SqlServer_Keys<\/td>\n<\/tr>\n
    20<\/th>\ndata.Cluster_SqlServer_Services_Keys<\/td>\n<\/tr>\n
    21<\/th>\ndata.Cluster_SqlServer_SqlProcess_Keys<\/td>\n<\/tr>\n
    22<\/th>\ndata.Cluster_SqlServer_TopQueries_Keys<\/td>\n<\/tr>\n
    23<\/th>\ndata.Cluster_SqlServer_Trace_Keys<\/td>\n<\/tr>\n
    24<\/th>\ndata.Group_Keys<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    The full object type hierarchy looks like this:<\/p>\n<\/p>\n
      \n
    •      Cluster\n
        \n
      •          Machine\n
          \n
        • LogicalDisk<\/li>\n
        • Network<\/li>\n
        • Process<\/li>\n
        • Services<\/li>\n<\/ul>\n<\/li>\n
        •          ResourceGroup\n
            \n
          • Resource<\/li>\n<\/ul>\n<\/li>\n
          •          SqlServer\n
              \n
            •              Agent\n
                \n
              •                  Job\n
                  \n
                • History<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n
                •              Database\n
                    \n
                  •                  BackupType\n
                      \n
                    • Backup<\/li>\n<\/ul>\n<\/li>\n
                    • CustomMetric<\/li>\n
                    • File<\/li>\n
                    •                  Table\n
                        \n
                      • Index<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n
                      • Error<\/li>\n
                      • Services<\/li>\n
                      • SqlProcess<\/li>\n
                      • TopQueries<\/li>\n
                      • Trace<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n
                      • Group<\/li>\n<\/ul>\n
                        Okay, but what about the individual objects themselves represented at each level in this hierarchy? Well that’s what the _Keys<\/em> 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:<\/p>\n
                        SELECT clstr._Name AS cluster_name,\n    srvr._Name AS instance_name,\n    db._Name AS database_name\n  FROM data.Cluster_SqlServer_Database_Keys db\n  JOIN \n    data.Cluster_SqlServer_Keys srvr\n  ON\n    db.ParentId = srvr.Id  -- Note here how the parent of a Database is a Server\n  JOIN \n    data.Cluster_Keys clstr\n  ON\n    srvr.ParentId = clstr.Id -- Note here how the parent of a Server is a Cluster\n  WHERE clstr._Name = 'dev-chrisl2' -- This is the hostname of my own PC\n  ORDER BY clstr._Name,\n    srvr._Name,\n    db._Name;<\/pre>\n
                        \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
                         <\/th>\ncluster_name<\/th>\ninstance_name<\/th>\ndatabase_name<\/th>\n<\/tr>\n
                        1<\/th>\ndev-chrisl2<\/td>\n<\/td>\nSqlMonitorData<\/td>\n<\/tr>\n
                        2<\/th>\ndev-chrisl2<\/td>\n<\/td>\nmaster<\/td>\n<\/tr>\n
                        3<\/th>\ndev-chrisl2<\/td>\n<\/td>\nmodel<\/td>\n<\/tr>\n
                        4<\/th>\ndev-chrisl2<\/td>\n<\/td>\nmsdb<\/td>\n<\/tr>\n
                        5<\/th>\ndev-chrisl2<\/td>\n<\/td>\nmssqlsystemresource<\/td>\n<\/tr>\n
                        6<\/th>\ndev-chrisl2<\/td>\n<\/td>\ntempdb<\/td>\n<\/tr>\n
                        7<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nSqlMonitorData<\/td>\n<\/tr>\n
                        8<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nTestDatabase<\/td>\n<\/tr>\n
                        9<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nmaster<\/td>\n<\/tr>\n
                        10<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nmodel<\/td>\n<\/tr>\n
                        11<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nmsdb<\/td>\n<\/tr>\n
                        12<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\nmssqlsystemresource<\/td>\n<\/tr>\n
                        13<\/th>\ndev-chrisl2<\/td>\nsql2005<\/td>\ntempdb<\/td>\n<\/tr>\n
                        14<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\nSqlMonitorData<\/td>\n<\/tr>\n
                        15<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\nmaster<\/td>\n<\/tr>\n
                        16<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\nmodel<\/td>\n<\/tr>\n
                        17<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\nmsdb<\/td>\n<\/tr>\n
                        18<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\nmssqlsystemresource<\/td>\n<\/tr>\n
                        19<\/th>\ndev-chrisl2<\/td>\nsql2008<\/td>\ntempdb<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
                        These results show that I have three SQL Server instances on my machine (a default instance, one named sql2005<\/em> and one named sql2008<\/em>), and each instance has the usual set of system databases, along with a database named SqlMonitorData<\/em>. 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:<\/p>\n
                          \n
                        1. Each _Keys<\/em> table has a column named Id<\/em>. This is the primary key.<\/li>\n
                        2. Each _Keys<\/em> table has a column named ParentId<\/em>. A foreign key relationship is defined between each _Keys<\/em> table and its parent _Keys<\/em> table in the hierarchy. There are two exceptions to this, Cluster_Keys<\/em> and Group_Keys<\/em>, because clusters and groups live at the root level of the object hierarchy.<\/li>\n
                        3. Each _Keys<\/em> table has a column named _Name<\/em>. This is used to uniquely identify objects in the table within the scope of the same shared parent object.<\/li>\n<\/ol>\n
                          Actually, that last item isn’t always true. In some cases, the _Name<\/em> column is actually called something else. For example, the data.Cluster_Machine_Services_Keys<\/em> table has a column named _ServiceName<\/em> instead of _Name<\/em> (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<\/em> (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.<\/p>\n
                          Well, that’s it for now. I’ve given you enough information for you to explore the _Keys<\/em> 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<\/em> and _UnstableSamples<\/em> tables. If you have any questions about this post, or suggestions for future posts, just submit them in the comments section below.<\/p>\n","protected":false},"excerpt":{"rendered":"
                          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……<\/p>\n","protected":false},"author":37766,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-4160","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/4160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/37766"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=4160"}],"version-history":[{"count":69,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/4160\/revisions"}],"predecessor-version":[{"id":42210,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/4160\/revisions\/42210"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=4160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=4160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=4160"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=4160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}