Mining Performance Data from SQL Sentry (Part 1)

Introduction

SQL Sentry Client Services encompasses both our Professional Services group and our Technical Support group. Between the two there are often requests for assistance mining performance metrics from the repository database. We don't publish an ERD mainly because we reserve the right to change it, sometimes dramatically, whenever we need, but we are always happy to help folks with information on how to get to their performance data. After all, in the end, it is their data, and they should have a way to get to it to use in any way they see fit right?

With that in mind, I read an article that Aaron Bertrand (B|T) did for mssqltips a while back, and it gave me the idea to post a series on how to get at this data. This needs to be a series, because it really is a long topic, and I can break it into parts to be digested much easier than throwing it all out at once.

Foundation Data

Before we can do anything else, we have to start with what servers and connections are being monitored, and with what products. The schema for this goes all the way back to the earliest versions of Event Manager, so the information below can also be used to get basic information for servers if you're only using Event Manager as well.

Computers

Every system added to SQL Sentry has to have a computer. Normally this is going to be a Windows server or workstation, and every computer SQL Sentry knows about is listed in the Device table.

It's important to note that the column "FullyQualifiedDomainName" is used by Performance Advisor to connect to native services like WMI and PerfLib. This can be directly manipulated to force connection to another server or specific IP address. We have done this in some cases when name resolution is not possible for one reason or another. I don't recommend hacking this unless you have a genuine need, but it's good information to have.

There are some other columns of interest such as the SiteID and ManagementEngineID that are important to continuous monitoring, but I'll get into those much later in this series.

The basic use of this table in mining data would be for the Host, IP Address and Platform. It really just lets you know what machine you're dealing with.

When we get into performance counters, we'll also see that there are some cases where we can use only the device table, without including the connections table listed below.

Connections

Next, connections are the various applications supported for monitoring. There are several, but the most common are SQL Server, Analysis Services and Windows. Information on connections is stored in the "EventSourceConnections" table. There is a one to many relationship between computers and connections, since a single computer could be a Windows server with say 3 SQL Server instances, or a single SQL Server instance and SSAS. You get the picture.

This table is a bit wider than the Device table eh? That's because there is a lot more to worry about at the connection level. Let's talk about that a bit.

Most columns in this table are for status and control of monitoring, but some useful columns in terms of how your SQL Sentry licenses are deployed can be pulled using the IsWatched, IsPerformanceAnalysisEnabled and IsFragmentationManagementEnabled columns. They correspond to Event Manager, Performance Advisor, and Fragmentation Manager licenses directly. If they are set to 1, you're currently using a license of that product for the connection.

The DeviceID column is our foreign key back to the Device table, so this is where we join to see what connections are on a computer.

The EventSourceConnectionTypeID column is important as we'll see in a moment. It will let us know what type of connection we're dealing with, so that we can know later what counters make sense for this connection. For example, you wouldn't want to be trying to collect compiles and recompiles for a Windows connection that is only monitored with Event Manager.

Connection types

The EventSourceConnectionType table simply lets us know what connection types are available, and it has a foreign key for its ObjectID in the EventSourceConnection table. It's useful for reporting, but again, critical for monitoring.

Putting it together

So, now that we know about these foundational tables in the SQL Sentry database, let's put them all together in a simple query that tells us what systems we're monitoring in SQL Sentry, and with what products.

I'm using a temp table here just to avoid making the join to ConnectionType over and over, but you could use a CTE or, just inline the subquery if you want. I think it's really just personal preference.

USE SQLSentry;
GO

SELECT ect.Name AS ConnectionType
     , c.*
     , d.FullyQualifiedDomainName
INTO #tmpConnections
FROM EventSourceConnection c
INNER JOIN Device d
     ON d.ID = c.DeviceID
INNER JOIN EventSourceConnectionType ect
     ON ect.ObjectID = c.EventSourceConnectionTypeID;

SELECT COUNT(ID) WatchedCount
     , ConnectionType
     , LicenseType
     , FullyQualifiedDomainName
     , ServerName
FROM (
     SELECT c.ID
          , c.ConnectionType
          , c.FullyQualifiedDomainName
          , c.ServerName
          , 'PA' AS LicenseType
     FROM #tmpConnections c
     WHERE c.IsPerformanceAnalysisEnabled = 1
     
     UNION ALL
     
     SELECT c.ID
          , c.ConnectionType
          , c.FullyQualifiedDomainName
          , c.ServerName
          , 'EM'
     FROM #tmpConnections c
     WHERE c.Iswatched = 1
     
     UNION ALL
     
     SELECT c.ID
          , c.ConnectionType
          , c.FullyQualifiedDomainName
          , c.ServerName
          , 'FM'
     FROM #tmpConnections c
     WHERE c.IsFragmentationManagementEnabled = 1
     ) WatchedConnections
GROUP BY WatchedConnections.ConnectionType
     , WatchedConnections.LicenseType
     , WatchedConnections.FullyQualifiedDomainName
     , WatchedConnections.ServerName
ORDER BY WatchedCount DESC;

DROP TABLE #tmpConnections;

And the output on my extremely boring test workstation that is only monitoring one server:

In a larger environment with, say, 300 to 600 monitored servers, this is a nice way to quickly see how your licenses are allocated. Now, we do have a way to do that in the SQL Sentry client as well, but where's the fun in that when we can write queries to do it!?

Also note that I am aware my aggregates are kind of pointless here since I've included the server's name from the connections table but take that out and you'll get a nice count of license usage per computer. Take the FullyQualifiedDomainName out and you'll get a count by license type for the entire SQL Sentry install.

Next Time

In part 2 of this series, I'm going to take us into some Performance Advisor specific data about the operating system. I'll be trying to keep these short, so we'll add one or two tables each time, and generally build on what we've learned previously.

Until next time,
-JRH

Mining Performance Data Series

• Part 1: Mining Performance Data from SQL Sentry (Part 1) ← You are here.
• Part 2: Mining Performance Data from SQL Sentry (Part 2) 
• Part 3: Mining Performance Data from SQL Sentry (Part 3) 
• Part 4: Mining Performance Data from SQL Sentry (Part 4) 
• Part 5: Mining Performance Data from SQL Sentry (Part 5) 
• Part 6: Mining Performance Data from SQL Sentry (Part 6)