1

One thing I miss from using SQL Profiler is selecting multiple rows in profiler (looking at Batch Completed and RPC Completed events) and seeing all the SQL in the below panel, i.e. as one combined text field with all the SQL statements. I could copy it all and paste into SQL Management Studio and work on it there. Is there an easy way to do similarly with Extended Events?

When I do an extended events session, e.g. using XEvent Profiler > Standard, it's a much less friendly UX to get all the SQL Statements generated. I have to double-click on each one, click the Copy All button on the popup, close that popup, then double-click on the next row. I could right-click on the row and choose Copy > Cell but that doesn't preserve line breaks in the copied text. If I've got more than a few events this isn't fun.

Am I missing something? How does everyone else use Extended Events without becoming super frustrated with the UX? I'm such an occasional user of it I've put off learning all the ins and outs of scripting event sessions and doing clever things with the results.

1 Answer 1

1

There is a way to move the fields from the detail page down below, up to the grid above. Once it's in the grid, you can sort, filter, group, export, pretty much anything you want.

There are a couple of ways to do this. One is, right click on the field in the bottom pain. In the context menu, select "Show column in table". That will move it up to the top where you can control things from there. Second method is to look at the tool bar for the Data Explorer window. There's a button there, near the far right side by default, labeled "Choose Columns...". Selecting that will open a window showing all the fields for the events currently captured. You can pick which ones you want in the upper table.

From there, it's just a question of using the table functions. Look again to the toolbar for all the stuff I mentioned above. For more details, here is a video I did for GroupBy. There is a whole bunch of other stuff at my blog on this topic.

7
  • Thanks Grant, but I still can't find a way to easily copy the text from several lines within the grid. Am I missing something obvious? Let's say there are 20 statements I want to copy the text from so I can edit & run in SSMS. What's an easy way to do that? Exporting ignores the rows I have selected and means I have to go find another file or table, get the rows I need, and copy & paste to SSMS. Also CSV loses line breaks. Exporting is only available when I've stopped the event session, I think? In the grid I can group but can't aggregate text fields (except COUNT)....
    – Rory
    Jun 29, 2021 at 19:08
  • ... Right-click > Copy > Cell only captures a single cell and for some reason loses line breaks. I can't see how to open the text details popup with the keyboard, so I can't come up with key combinations that'd do it. I can't see any keyboard commands that would be handy. You can't select rows within the grid. None of the toolbar buttons for the grid or the Extended Events main menu options seem to be much use. What am I missing?
    – Rory
    Jun 29, 2021 at 19:17
  • Copy loses the line breaks, but you can just export it too. That's how I'd do it. Jun 30, 2021 at 20:16
  • @GrantFritchey Exporting the XEL to CSV results in horrendously corrupted output (looks like SSMS isn't escaping quotes in sql_text or statement in the output CSV file... yikes). I also tried Export-to-Table but that doesn't work with Azure SQL, it only seems to work with on-prem SQL Server instances *grumble*
    – Dai
    Oct 30, 2023 at 5:28
  • @GrantFritchey Just to follow-up to say that Export-to-Table does seem to preserve line-breaks in statement and sql_text - phew - I just had to faff around with spinning up a new MSSQL box. UPDATE ah crap, it truncates the values to 65,537 characters - this is annoying because I'm trying to troubleshoot a 300KB .sql batch.
    – Dai
    Oct 30, 2023 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.