July 29, 2020 at 4:32 pm
I have a 300 Meg .XEL file from an Entended Event session that I used recently. I stopped the session, and want to query the data. What's the best way to get the file into a SQL table ?
In general, what's the best way to read the data, and look for stored procedure calls etc ??
I ran this, but not sure if there are better ways, options, functions etc...
select event_data = CONVERT(xml, event_data)
INTO MyDatabase..EE_File_20200729
from sys.fn_xe_file_target_read_file('D:\SQL Server 2016 SP2\TraceFiles\SQL_Activity_EE_0_132405090832580000.xel', null, null, null)
July 30, 2020 at 11:58 am
There are three ways to access the data in Extended Events. You can use the Live Data window within SSMS. It works really well and has a lot of functionality such as the ability to search, filter and aggregate the data. I have a video available here, another here, and a bunch of blog posts on how to use it.
Next, you're already looking at. The function fn_xe_file_target_read_file will allow you to query directly against the events captured. Now, to really use that data, you've got to do some XQuery. I've got examples on my blog and there are others elsewhere.
Another method would be to use the functionality built into DBATools. There are a bunch of explicit commands that let you retrieve data from your session output without having to go through the XQuery mess. Here's a good explanation.
So, in terms of overall ease, it's SSMS, then DBATools, then the XQuery stuff. In terms of absolute power to control the info, it's DBATools, XQuery, then SSMS. I use SSMS the most because it's simple and most of the time, simple answers to simple questions are what we need. However, when I really want to dig deep on the data, you need to query it. The easy way to query it is DBATools.
Hope all that helps.
"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
July 30, 2020 at 1:50 pm
It looks like SSMS Live Data option only works if the session is started or paused ? I did "Stop Session" and want to filer and narrow down data. I don't see "Pause". Must've missed it. Looking at DBATools video, but just more confused.
July 30, 2020 at 2:27 pm
No. You should be able to open a live file. I do it all the time.
Oh wait, I see what you said. If you want to aggregate, you do have to hit the red stop button. That doesn't stop the session. That stops the live feed of the session. Totally different.
"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
July 30, 2020 at 2:42 pm
Is there a GUI method to browse, filter the EE_20200729.XEL file I created yesterday ?
I am trying to find some stored procedure calls, triggers and table updates, invoked by a 3rd party application.
July 30, 2020 at 2:48 pm
File... Open.
That's it. It's really straight forward.
"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
July 30, 2020 at 8:29 pm
Thanks. Now the REAL problem is weeding through all the data. I captured production events for 30 minutes, and got 1 million records.
Then I decided to capture some data changes in the TEST environment, hoping to get less activity. The extended events ran for 3 minutes, resulting in 1.4 million records !...Crazy.
For example. somehow the application managed to execute this statement "return( @dReturnDate )" 19,700 times in less than a minute. Every row has a slightly different timestamp, so it's not duplicated data on my part.
July 31, 2020 at 10:40 am
Yeah, the curse and blessing of capturing the queries is you get to see exactly what's going on. Little things like that 19,700 executions accumulate in an insane manner to hurt the overall performance of the system. It's why buying/building/implementing good monitoring is so terribly important. Best of luck on it.
"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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply