Fun with JSON, SQL Server, and SentryOne (JSON, not Jason)

What Am I Doing and Why?

I wanted to dig into the JSON functions in SQL Server, which were added in SQL Server 2016. I’m still doing web development backed by services when duties allow it, and anyone living in that world will know how ubiquitous JSON is. SentryOne also uses it a lot more than one might imagine, and I figured the best way to dig into this and make it stick would be to do something potentially useful with it for SentryOne users leveraging SentryOne data.

To bottom line it, I’m going to take a SentryOne Advisory Condition that uses performance counters, sniff out the left and right sides of an expression, then output some history for counters on both sides of the expression. The JSON part comes early, where the definition of the Advisory Condition is a JSON string in the SentryOne database.

I don’t want to go too crazy with this just yet, so to keep it simple, I’ll be using a super small Advisory Condition to guarantee it has the shape I’m looking for. That means we’ll start by defining the Advisory Condition.

The Advisory Condition

What I want here is a condition that actually makes sense to use, but also doesn’t really include a ton of logic that I need the Advisory Conditions evaluation engine to do for me. I want to compare counters that are fairly volatile as well, so my data isn’t overly boring.

CPU is volatile, and SQL Server waits are volatile. It also makes perfect sense to compare resource waits against CPU (signal) waits. This essentially answers the question, “Am I waiting on CPU more than resources?” So, I made a little Advisory Condition that tests for this across wait types.

Here is the visual definition:

The image is a bit small, I know, so let’s look at the JSON representation from the SentryOne database table “DynamicConditionDefinition” for something easier to read.

Here is the JSON signature for my Advisory Condition:

{
    "OperationTypeID": "6c3d38f1-9317-4258-972a-cfee0a0d76b5",
    "Children": [
        {
            "OperationTypeID": "8fa3cd6a-960c-43bb-96e0-94092fc2c296",
            "ValueDataTypeID": "b35a57f5-b8f6-4e4e-9380-e9d328e3eabf",
            "Left": {
                "ValueTypeID": "958b13fe-3149-4ee0-9104-edc0012f7566",
                "PerformanceCategoryType": 1,
                "CounterID": -228,
                "CounterName": "Waits by Type: Resource",
                "CategoryName": "SQL Server Wait Statistics",
                "InstanceType": 0,
                "InstanceName": null,
                "PerformanceCounterValueRetrievalType": 0,
                "BaselineAggregationMode": null,
                "CustomBaselineID": null,
                "GlobalBaselineName": null
            },
            "ComparisonType": 5,
            "Right": {
                "ValueTypeID": "958b13fe-3149-4ee0-9104-edc0012f7566",
                "PerformanceCategoryType": 1,
                "CounterID": -229,
                "CounterName": "Waits by Type: CPU",
                "CategoryName": "SQL Server Wait Statistics",
                "InstanceType": 0,
                "InstanceName": null,
                "PerformanceCounterValueRetrievalType": 0,
                "BaselineAggregationMode": null,
                "CustomBaselineID": null,
                "GlobalBaselineName": null
            },
            "ID": "1"
        }
    ],
    "BooleanOperationType": 0,
    "ID": "0"
}

This has a bunch of stuff in it that tells the evaluation engine how to treat the condition. It’s all very interesting, but what I care about right now is the expression. There could be several expressions, but for this one there is only one. That one expression looks at Resource Waits on the left, and CPU waits on the right. A comparison is made, but for the purpose of this article, we’re not paying attention to the comparison. Let’s let SentryOne’s evaluation engine deal with that.

So, really all we are looking for from this is the following:

The counter from the left:

                "CounterName": "Waits by Type: Resource"
                "CounterID": -228

The counter from the right:

                "CounterName": "Waits by Type: CPU"
                "CounterID": -229

You might be wondering why those CounterIDs have negative values. I’ll get to that, but it’s a very good question, so kudos to you for noticing!

Circling back to the inspiration for this article, we can use the SQL Server JSON functions to get these values for us without all the other stuff:

select 
    JSON_VALUE(RuleDefinition, '$.Children[0].Left.CounterName') [counter-name-left],
    JSON_VALUE(RuleDefinition, '$.Children[0].Left.CounterID') [counter-id-left],
    JSON_VALUE(RuleDefinition, '$.Children[0].Right.CounterName') [counter-name-right],
    JSON_VALUE(RuleDefinition, '$.Children[0].Right.CounterID') [counter-id-right]
from 
    DynamicConditionDefinition
where 
    [Name] = 'JSON Test';

(Look @AaronBertrand – there’s a semicolon terminating the statement!)

The structure of the JSON states that from the root ($) we have an array (Children). Each child in Children has a left side (Left) and a right side (Right). Left and Right both have CounterName and CounterID. The query above translates to, “give me CounterName and CounterID on Left and Right from the first (0 index) item in the Children array.”

Here is the output from that query:

You can see this gives me exactly what I want. Incidentally, Advisory Condition definitions do not always take this exact shape. I’ve carefully created the perfect scenario for this query to deliver what I want. If I do this against all of my Advisory Conditions, I get a lot of null values:

I’m primarily pointing this out to show you that I get nulls and not errors. This is important to understand because it lets me know that there is some forgiveness built into how the JSON functions handle things. As long as I am asking for something that could be valid in JSON, it won’t blow up on me. If your query is blowing up, you need to look at “how” you are asking. If it’s giving you nulls, you should look at “what” you are asking for.

There is a lot more you can do with JSON in SQL Server than what I’m doing here, but we’ll save more for later. This is going to get long enough when I get into why those CounterIDs are negative numbers. If you want to learn all about the JSON functions, you should check out this Microsoft resource. Just do it after you read this blog, because I have a lot more to go through.

Let’s Get Some Data!

Normally when I have a CounterID in SentryOne, the schema I need for querying values is somewhat intuitive. Intuitive doesn’t mean obvious, but you can figure it out with some digging. Things are generally named after what they are for this very purpose. You can also take a trip back in time to use these SentryOne blog posts with confidence that they are still relevant today.

There is an exception. Remember those negative CounterIDs? Those have very real meaning in SentryOne. If you ever run across them, it indicates that you are dealing with what we call “Virtual Performance Counters.” The least complicated way I can explain this is that it is a mechanism that allows SentryOne to track an abstract or projected view of performance data. I won’t get into too much detail here for fear of the IP police arresting me, but in this specific case, it means that I need to take an extra step or two to get what I need because these counters are likely made from multiple other sources. The database table “PerformanceAnalysisVirtualCounter” will tell me how to get the values.

Let’s have a look at that:

Things I care about from this table are “DataSource” and “RawCounterID.” The developers who added these counters saw into the distant future. I’m 100% certain they said, “One day, Jason will need to use these for an article. Let’s make it easy on him and provide stored procedures to use.” This sort of thing happens all the time at SentryOne. Our developers are organized according to their precognitive ability. Moving on…

DataSource tells me what to use to dig up the values I want, and RawCounterID tells me what actual (meaning not virtual) CounterID to use when something in the DataSource asks for a CounterID. These are two key pieces that I need to keep going.

The procedures from DataSource both have the same signature:

Here’s a table describing these parameters:

A simple call to one of them looks like this, which I snagged from a SentryOne QuickTrace while I was refreshing the performance dashboard:

exec dbo.GetCounterSQLResourceWaitsDataRangeByTimestamp @DeviceID=1,@ConnectionID=2,@CounterID=1172,@StartTimestamp=126138502,@EndTimestamp=126138502,@RangeSizeMinutes=0

Output looks like this:

Note that we’re getting waits by their type. I want granular detail here, but due to the clever and organized treatment of wait statistics by SentryOne internals, I could scope this to a higher level. This is one of the things that makes SentryOne monitoring special. Waits are something you know to look at, but you can get lost in them. SentryOne starts by summarizing waits into categories for you. Then classification takes it down to a slightly lower level. Finally, when you need to go down the rabbit hole, you can go as deep as you like with the specific wait types. All the relevant types are there with the less useful ones held back, so there is no garbage cluttering up your rabbit hole. I like the rabbit hole, so we jumped right in.

Because I want to use this elsewhere, I messed about with it to get something a bit more malleable. This isn’t normally my coding style. I used a scripting option from Azure Data Studio (try it, you will like it), and I worked on it a bit from there.

DECLARE @DeviceID smallint = 1;
DECLARE @ConnectionID smallint = 2;
DECLARE @CounterID smallint = 1172;
DECLARE @StartTimestamp int;
DECLARE @EndTimestamp int;
DECLARE @RangeSizeMinutes int;

select @StartTimestamp = 
    dbo.fnConvertDateTimeToTimestamp(DATEADD(HOUR,-1,GETDATE()));
    --Told you there is a function for that

select @EndTimestamp = 
    dbo.fnConvertDateTimeToTimestamp(GETDATE());
    --and that ;)

EXEC [dbo].[GetCounterSQLResourceWaitsDataRangeByTimestamp] 
   @DeviceID
  ,@ConnectionID
  ,@CounterID
  ,@StartTimestamp
  ,@EndTimestamp
  ,@RangeSizeMinutes;

Now, let’s take inventory. We have:

1. Advisory Condition JSON
2. Left and Right virtual counters from the AC expression
3. Raw counter from the virtual counter
4. Working stored procedures for both Right and Left

What else do we need? Well, this all feels a bit disjointed, so let’s bring it together. Brace yourself.

use SentryOne;
GO

-- variables
declare @DeviceID smallint;
declare @ConnectionID smallint;
declare @CounterID smallint;
declare @StartTimestamp int;
declare @EndTimestamp int;
declare @RangeSizeMinutes int;

declare @LeftCounter int;
declare @RightCounter int;
declare @LeftName nvarchar(256);
declare @RightName nvarchar(256);
declare @LeftCounterSource nvarchar(256);
declare @RightCounterSource nvarchar(256);

--The parts I need from my advisory condition
select 
    @LeftName = JSON_VALUE(RuleDefinition, '$.Children[0].Left.CounterName'),
    @LeftCounter = JSON_VALUE(RuleDefinition, '$.Children[0].Left.CounterID'),
    @RightName = JSON_VALUE(RuleDefinition, '$.Children[0].Right.CounterName'),
    @RightCounter = JSON_VALUE(RuleDefinition, '$.Children[0].Right.CounterID')
from 
    DynamicConditionDefinition
where 
    [Name] = 'JSON Test'; --Name of my Advisory Condition

--The virtual counter info
select
    @LeftCounter =  RawCounterID,
    @LeftCounterSource = [DataSource]
from 
    PerformanceAnalysisVirtualCounter
where 
    ID = @LeftCounter;

select
    @RightCounter =  RawCounterID,
    @RightCounterSource = [DataSource]
from 
    PerformanceAnalysisVirtualCounter
where 
    ID = @RightCounter;

--How to get my deviceID and connectionID
select 
    /*Device.HostName, -- Just making sure I have the right one
    Device.IPAddress, -- Making doubly sure I have the right one */
    @DeviceID = Device.ID,
    @ConnectionID = EventSourceConnection.ID
from 
    Device 
    join EventSourceConnection on 
        EventSourceConnection.DeviceID = Device.ID
    join EventSourceConnectionType on 
        EventSourceConnectionType.ObjectID = EventSourceConnection.EventSourceConnectionTypeID
where 
    Device.FullyQualifiedDomainName = 'EC2AMAZ-MPHPESQ' -- Just a test box on AWS EC2
    and EventSourceConnectionType.[Name] = 'SQL Server'

--A temp table to hold my values
create table #CounterData
    ([TimeStamp] DateTime, [Value] float, InstanceName nvarchar(256));

--A temp table to assemble my output
create table #ExpressionData
    ([TimeStamp] DateTime, LeftCounterName nvarchar(256), LeftValue float, RightCounterName nvarchar(256), RightValue float);

select @StartTimestamp = 
    dbo.fnConvertDateTimeToTimestamp(DATEADD(MINUTE,-5,GETDATE())); 
    -- Do 5 minutes to keep it small for the article
    --Told you there is a function for that

select @EndTimestamp = 
    dbo.fnConvertDateTimeToTimestamp(GETDATE());
    --and that :)

-- Need this for sp_executesql
declare @sqlCall nvarchar(512);
set @sqlCall = @LeftCounterSource + N' @DeviceID, @ConnectionID, @CounterID, @StartTimestamp, @EndTimestamp ';

--the left side
insert #CounterData 
    exec sp_executesql 
        @sqlCall,
        N'@DeviceID smallint,
        @ConnectionID smallint,
        @CounterID smallint,
        @StartTimestamp int,
        @EndTimestamp int',
        @DeviceID = @DeviceID,
        @ConnectionID = @ConnectionID,
        @CounterID = @LeftCounter,
        @StartTimeStamp = @StartTimeStamp,
        @EndTimeStamp = @EndTimeStamp;

--start my expression data
insert #ExpressionData
    select 
        [Timestamp],
        InstanceName,
        [Value],
        null,
        null
    from 
        #CounterData;
--clear out counterdata to use it again
truncate table #CounterData;

--update the sp_executesql call to get the right side
set @sqlCall = @RightCounterSource + N' @DeviceID, @ConnectionID, @CounterID, @StartTimestamp, @EndTimestamp ';

--put that in the, now empty, counterdata table
insert #CounterData 
    exec sp_executesql 
        @sqlCall,
        N'@DeviceID smallint,
        @ConnectionID smallint,
        @CounterID smallint,
        @StartTimestamp int,
        @EndTimestamp int',
        @DeviceID = @DeviceID,
        @ConnectionID = @ConnectionID,
        @CounterID = @RightCounter,
        @StartTimeStamp = @StartTimeStamp,
        @EndTimeStamp = @EndTimeStamp;

--run an update on the expression data to fill in what we have for the right side matches
update 
    #ExpressionData
set 
    RightCounterName = InstanceName,
    RightValue = [Value]
from 
    #CounterData
    join #ExpressionData on 
        #ExpressionData.[TimeStamp] = #CounterData.[TimeStamp]
        and #ExpressionData.LeftCounterName = #CounterData.InstanceName;

--remove some unmatched rows I don't want
delete #ExpressionData where RightValue is null;

--A final query to see what we have and add a little comparison column
select 
    [Timestamp],
    LeftCounterName [WaitType],
    LeftValue [Resource],
    RightValue [CPU],
    CAST((RightValue / LeftValue) * 100 as int) [% CPU]
from 
    #ExpressionData
order by 
    [Timestamp] asc,
    WaitType asc;

--cleanup
drop table #CounterData;
drop table #ExpressionData;

And it isn’t complete unless we can see some results:

The End, for Now

This is where messing with JSON functions got me, folks. It’s a bit longer than I planned, but this is really just how I learn things. It helps retention immensely if I can apply it to something that might be useful. There are certainly other ways to get this data, including just looking at it in your SentryOne GUI or reports. I think approaching it from the angle of the Advisory Condition is interesting, and if you try this, you’ll see that it is pretty snappy. That is likely because it uses the built-in stored procedures to gather the counter data. They are optimized where some random query I wrote likely would not be.

I’m definitely not done with JSON in SQL Server. Maybe next time I’ll see what I can make for all those Advisory Conditions where I don’t already know exactly how the signature is shaped.

Lastly, please remember it’s JSON, not Jason. 😊

Jason Hall

Jason has worked in technology for over 20 years. He joined SentryOne in 2006 having held positions in network administration, database administration, and software engineering. During his tenure at SentryOne, Jason has served as senior software developer and founded both Client Services and Product Management. His diverse background with relevant technologies made him the perfect choice to build out both of these functions.

As SentryOne experienced explosive growth, Jason returned to lead SentryOne Client Services, where he ensures that SentryOne customers receive the best possible end to end experience in the ever-changing world of database performance and productivity.