January 14, 2016 at 10:08 am
So I tried setting up my first Extended Events session, just to see if I could get the basics working. I used SSMS to create the session, but here's the scripted equivalent. I was trying to keep things simple, and essentially capture every SQL statement running through the system (at least, that's what I thought selecting
"sql_statement_completed" would do):
CREATE EVENT SESSION [RecordAllQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
When I right-click on the session, select Watch Live Data, and then go run some SQL statements, nothing shows up in the Live Data tab that popped up. I'm thinking there's something really simple I'm overlooking, but I'm not sure what it is.
Thanks for any help,
--=Chuck
January 14, 2016 at 10:24 am
MAX_DISPATCH_LATENCY=30 SECONDS
Events don't show up instantly in the live data view. They're not dispatched instantly either. Run your queries, wait a bit (oh and make sure that you started the session after you created it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2016 at 10:38 am
Ah, patience apparently isn't one of my virtues today. I'm seeing the events stack up now.
Thanks,
--=Chuck
January 14, 2016 at 10:39 am
Overall your session is missing a target. If you do not provide one not sure what target it defaults to.
Will also note though that opening the Live Data Viewer attaches a different target to your session called "streaming target". This will also cause the dispatch latency associated with your session to change to 3 seconds. However targets are either synchronous or asynchronous, and dispatching only occurs under asynchronous targets. There are two conditions that will cause the data to be dispatched: (1) memory buffer allocated for the session fills up or (2) event data in the buffer exceeds the sessions MAX_DISPATCH_LATENCY value.
Which the memory buffer I mention is created when the session starts up and defaults to 4MB, you control this with the MAX_MEMORY setting.
I wrote this up in more detail on another forum site you can view here. All of this I picked up from watching the Pluralsight courses Jonathan Keyhayias.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2016 at 11:43 am
Looks like it defaults to something called an "event stream". At least that's what's listed when I run your amended SQL stmt as such, and thanks, btw 🙂
SELECT [t].[target_name]
FROM [sys].[dm_xe_sessions] AS s
JOIN [sys].[dm_xe_session_targets] AS t
ON [t].[event_session_address] = .[address]
WHERE .[name] = N'RecordAllQueries'
That's not an option when selecting Targets, but, I am still able to receive output.
January 14, 2016 at 11:58 am
I'm probably wrong, being so new to SQL Server and Extended Events, but the "event stream" target seems to only supply information if you're watching the live viewer. Otherwise, it's just up in the ether. At least, the only time the following query (again, thanks Shawn) returned any data was if the Live Viewer was currently running:
SELECT [t].[target_name], CAST([t].[target_data] AS XML)
FROM [sys].[dm_xe_sessions] AS s
JOIN [sys].[dm_xe_session_targets] AS t
ON [t].[event_session_address] = .[address]
WHERE .[name] = N'RecordAllQueries'
There was also no target displayed beneath the session within SSMS for this target type (ie, nothing you could right-click to "View Target Data").
When I switched it over to ring buffer, the above query displayed information whether or not the Live Viewer was open.
If I could ask a related follow-up ... is "View Target Data" your only option to view data, if you're looking for data generated before the live data viewer is opened?
--=Chuck
January 14, 2016 at 12:41 pm
chuck.forbes (1/14/2016)
I'm probably wrong, being so new to SQL Server and Extended Events, but the "event stream" target seems to only supply information if you're watching the live viewer. Otherwise, it's just up in the ether. At least, the only time the following query (again, thanks Shawn) returned any data was if the Live Viewer was currently running:
SELECT [t].[target_name], CAST([t].[target_data] AS XML)
FROM [sys].[dm_xe_sessions] AS s
JOIN [sys].[dm_xe_session_targets] AS t
ON [t].[event_session_address] =
.[address]WHERE
.[name] = N'RecordAllQueries'There was also no target displayed beneath the session within SSMS for this target type (ie, nothing you could right-click to "View Target Data").
When I switched it over to ring buffer, the above query displayed information whether or not the Live Viewer was open.
If I could ask a related follow-up ... is "View Target Data" your only option to view data, if you're looking for data generated before the live data viewer is opened?
--=Chuck
The event stream target is added when you are viewing live data. If you have a target configured you can view the data from the Live View or query it directly using T-SQL. There is a Stairway Series started on Extended Events [/url]but Erin has not gotten to view the data yet. There are a handful of examples online that can be found on querying the various targets, most common is to query "ring_buffer" probably.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2016 at 12:42 pm
chuck.forbes (1/14/2016)
Looks like it defaults to something called an "event stream".
Shawn Melton (1/14/2016)
Will also note though that opening the Live Data Viewer attaches a different target to your session called "streaming target".
Afaik, there's no default. No target means no target.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2016 at 12:45 pm
It's all coming together for me. Thanks for the assistance.
And in case it helps someone else out, I was asking about viewing the session data in a format other than right-clicking the target and choosing "View Target Data", because when I had the target type set to ring buffer, I was getting output in XML format. Well, it appears that the output is related to the target type selected. When I next chose event file as the target type, "View Target Data" then brought up the session information in a format which looks just like the "Watch Live Data" window.
--=Chuck
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply