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:
- Advisory Condition JSON
- Left and Right virtual counters from the AC expression
- Raw counter from the virtual counter
- 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.Left.CounterName'), @LeftCounter = JSON_VALUE(RuleDefinition, '$.Children.Left.CounterID'), @RightName = JSON_VALUE(RuleDefinition, '$.Children.Right.CounterName'), @RightCounter = JSON_VALUE(RuleDefinition, '$.Children.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. 😊