November 21, 2016 at 7:05 am
We have extended event definitions on all our non-prod servers, primarily to aid with debugging and development. At any point we can look to see a list of almost-real-time stored procedure calls.
The problem is, it's cumbersome to go through object explorer, find the server, expand the tree, and work our way into the Extended Events node, right-click our session and choose "Watch Live Data" (or otherwise access the data from the session).
Is there an easier way?
I think I could try to run a query and parse the XML, but that, too, is cumbersome in its own way (I don't particularly care for working with xml path). I use AutoHotKey religiously, and tried to set up a SendKeys macro to navigate through the object explorer tree, but I failed miserably. (In the olde days, I'd hit Ctrl+Alt+NumPad to fire up Profiler, connect to an instance, and start displaying data... all automated, up and going in about 3 seconds. I'm trying to wean myself off trace and use extended events)
I welcome any suggestions!
November 21, 2016 at 7:08 am
I've created views for all my Extended Events, so they are a little slow, but orders of magnitude easier to use.
yes the views shread the XML, but once written they are so easy to use; for me, filtering on timestamp is crucial
Lowell
November 21, 2016 at 7:14 am
here's the script i use to generate the CREATE VIEW scripts for all extended events on the server:;
it has a reference to master.dbo.DelimitedSplit, which is a version i made of the rather famous DelimitedSplit8K, except it's for varchar(max)
DECLARE @SessionName VARCHAR(128) = NULL; -- = 'DDLChanges'
--DECLARE @SessionName VARCHAR(128) = 'DDLChanges' -- = NULL; -- =
;WITH Prerequisite
AS
(
SELECT [sess].[name] As SessionName,
[targz].[target_name] As TargetName,
precmd = '
--#################################################################################################
-- ' + [sess].[name] + '
--#################################################################################################
IF OBJECT_ID(''[dbo].[vwExtendedEvent_' + [sess].[name] + ']'') IS NOT NULL
DROP VIEW [dbo].[vwExtendedEvent_' + [sess].[name] + ']
GO
CREATE VIEW vwExtendedEvent_' + [sess].[name] + ' AS
SELECT
n.value(''(@timestamp)[1]'', ''datetime2'') AS [timestamp]
,n.value(''(@name)[1]'', ''varchar(128)'') AS EventName
,n.value(''(@package)[1]'', ''varchar(128)'') AS PackageName
,n.value(''(@id)[1]'', ''int'') AS SessionIdentifier
,n.value(''(@version)[1]'', ''int'') AS PackageVersion',
pstcmd = 'FROM
(SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(N''' + [sess].[name] + '*.xel'',null, null, null)
) as tab
CROSS APPLY event_data.nodes(''event'') as q(n)
--#################################################################################################
GO
'
from [sys].[dm_xe_sessions] sess
INNER JOIN [sys].[dm_xe_session_targets] AS targz
ON [sess].[address] = [targz].[event_session_address]
WHERE [targz].[target_name] = 'event_file'
AND ([sess].[name] = @SessionName OR @SessionName IS NULL)
)
--SELECT * FROM Prerequisite
,MyDataCTE
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [obcolz].[name] ORDER BY (SELECT NULL)) AS RW,
[sess].[name] AS SessionName ,
[targz].[target_name] AS TargetName,
[evtz].[event_name] AS EventName,
[obcolz].[name] AS EventColumn,
[obcolz].[type_name] AS EventColumnDataType
FROM [sys].[dm_xe_sessions] sess
INNER JOIN [sys].[dm_xe_session_targets] AS targz ON [sess].[address] = [targz].[event_session_address]
INNER JOIN [sys].[dm_xe_session_events] evtz ON [sess].[address] = [evtz].[event_session_address]
INNER JOIN [sys].[dm_xe_object_columns] obcolz ON [evtz].[event_name] = [obcolz].[object_name]
WHERE [targz].[target_name] = 'event_file'
),
DataCommands
AS
(
SELECT DISTINCT
t.SessionName,
t.TargetName,
ISNULL(',' + sq.Columns,'') AS ScriptedNodes
FROM MyDataCTE t
JOIN (
SELECT
SessionName,
TargetName,
Columns = (STUFF((SELECT ',' + 'n.value(''(data[@name="' + EventColumn + '"]/value)[1]'', '''
+ CASE
WHEN EventColumnDataType = 'Int32'
THEN 'int'
WHEN EventColumnDataType = 'boolean'
THEN 'bit'
ELSE 'varchar(max)'
END +''') AS ' + EventColumn + + CHAR(13) + CHAR(10)
FROM MyDataCTE sc
WHERE sc.SessionName = s.SessionName AND sc.TargetName = s.TargetName AND sc.RW = 1
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
)
FROM MyDataCTE s
) sq
ON t.SessionName = sq.SessionName AND t.TargetName = sq.TargetName
),
MyActionsCTE
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY actz.action_name ORDER BY (SELECT NULL)) AS RW,
[sess].[name] AS SessionName ,
[targz].[target_name] AS TargetName,
actz.action_name
from sys.dm_xe_sessions sess
INNER JOIN sys.dm_xe_session_targets AS targz
ON sess.address = targz.event_session_address
AND targz.Target_name = 'event_file'
INNER JOIN sys.dm_xe_session_events evtz
ON targz.event_session_address = evtz.event_session_address
inner join sys.dm_xe_session_event_actions actz
ON targz.event_session_address = actz.event_session_address AND evtz.event_name = actz.event_name
WHERE [targz].[target_name] = 'event_file'
),
ActionCommands
AS
(
SELECT DISTINCT
t.SessionName,
t.TargetName,
ISNULL(',' + sq.Columns,'') AS ScriptedNodes
FROM MyDataCTE t
JOIN (
SELECT
SessionName,
TargetName,
Columns = (STUFF((SELECT ',' + 'n.value(''(action[@name="' + action_name + '"]/value)[1]'', ''varchar(max)'') AS '+ action_name + CHAR(13) + CHAR(10)
FROM MyActionsCTE sc
WHERE sc.SessionName = s.SessionName AND sc.TargetName = s.TargetName AND sc.RW = 1
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
)
FROM MyDataCTE s
) sq
ON t.SessionName = sq.SessionName AND t.TargetName = sq.TargetName
),TheData
AS
(
SELECT Prerequisite.SessionName,
Prerequisite.TargetName,
cmd = Prerequisite.precmd
+'
-- data
'
+ DataCommands.ScriptedNodes
+'
-- actions
'
+ ActionCommands.ScriptedNodes
+ Prerequisite.pstcmd
FROM Prerequisite
INNER JOIN DataCommands
ON Prerequisite.SessionName = DataCommands.SessionName
AND Prerequisite.TargetName = DataCommands.TargetName
INNER JOIN ActionCommands
ON Prerequisite.SessionName = ActionCommands.SessionName
AND Prerequisite.TargetName = ActionCommands.TargetName
)
SELECT * FROm TheData
CROSS APPLY master.dbo.DelimitedSplit(TheData.cmd,CHAR(13) + CHAR(10))
WHERE SessionName <> 'system_health'
Lowell
November 21, 2016 at 8:07 am
Assuming you're outputting to a file, you can just open the file and you'll be immediately viewing the live data. That may not be a lot easier, but it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2016 at 7:56 am
Lowell (11/21/2016)
I've created views for all my Extended Events, so they are a little slow, but orders of magnitude easier to use.yes the views shread the XML, but once written they are so easy to use; for me, filtering on timestamp is crucial
This looks helpful- not exactly what I was hoping for, but certainly a different (and sometimes more convenient!) means of looking at the data. Thanks for the response, Lowell.
November 22, 2016 at 8:00 am
Grant Fritchey (11/21/2016)
Assuming you're outputting to a file, you can just open the file and you'll be immediately viewing the live data. That may not be a lot easier, but it works.
Hey, this may be just the ticket.... I've found if I double-click an .xel in Explorer it opens a new instance of SSMS. But if I drag a file into (or open from within) SSMS I am good to go. I may need to address dynamic file names, but that can easily be scripted.
Now we're talking about one-click access to a particular session's data. Thanks!
If I come up with an ultra-clever solution that might be useful to others, I will post back here.
November 22, 2016 at 8:08 am
Idera has a collection of free tools, one of which is an Extended Event Profiler. I havn't gotten around to using it yet, but it sounds like what you're looking for.
.. IDERA’s SQL XEvent Profiler emulates the functionality and simplicity of SQL Server Profiler, letting you quickly view data from SQL events and see what's happening on the server. Plus, it leverages SQL Extended Events (XEvents) as the backing technology — making it more efficient, more powerful and more scalable than SQL Trace ..
https://www.idera.com/productssolutions/freetools/sqlxeventprofiler
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 22, 2016 at 8:24 am
Eric M Russell (11/22/2016)
Idera has a collection of free tools, one of which is an Extended Event Profiler. I havn't gotten around to using it yet, but it sounds like what you're looking for... IDERA’s SQL XEvent Profiler emulates the functionality and simplicity of SQL Server Profiler, letting you quickly view data from SQL events and see what's happening on the server. Plus, it leverages SQL Extended Events (XEvents) as the backing technology — making it more efficient, more powerful and more scalable than SQL Trace ..
https://www.idera.com/productssolutions/freetools/sqlxeventprofiler
I tried it out. It's a good basic tool that might be useful for some folks. But it doesn't allow me to access my extended event sessions (rather, it acts as a very simple replacement for some uses of Profiler). And unfortunately I am receiving errors while attempting to uninstall it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply