No tags yet.
Capture Query Information with Extended Events
Many of us have not made the effort to switch from profiler to Extended events. It's 2018, if you haven't found a few hours to learn about this incredibly powerful tool, I urge you to do so now.
I'm going to provide a quick means of tracking queries with extended events. This is not an example of how comprehensive this is, but I hope that it atleast spurs some interest.
One of the main reasons we use profiler is to quickly capture some real time data. I'm going to not only show you how to do that with extended events, but this same session can be a historical view as it's so easy to sift through and filter through the data. (No you don't have to create a table for the result sets ala profiler).
So, just open up SSMS, go to management -> Extended Events and right click on sessions. I've never used the wizard, so just go to the "new sessions" option.
I'm going to call it "Query Capture" and then move on to picking out our events.
You can easily search the event library (I'm adding sp_statement_completed and sql_statement_completed). Once that's done, hit the configure option at the top.
Go through each of the tabs at the top to set what Global fields you want, any filters that you may want to set and what event fields you want.
Int his case, i'm picking stuff like sql_text, query_plan_hash, database_name. Pick whatever you feel is relevant to you.
In the filter predicate, you can set certain options. For example, I want logical reads that are over 4000.
The event fields will provide what events will be displayed along with a few options.
In the data storage section, I like to save it to a file. I set my configurations as such:
Once you're finished, press ok. Then you have to start the session. Right click on the session and click "start session". simple enough.
Then you can right click and "Watch Live Data", just like profiler. Or you can "View target data" of the event file for everything that's been captured.
What I like to do, is go into the details pane at the bottom half of the screen. Right click on the relevant "field" and select "Show column in table". this makes it so that it's displayed.
From there, you can sift through your data. Feel free to play with the filtering options to filter via time, or any many other options.
Hope you found this intro to Extended events useful. Remember, don't be afraid to explore new features. it's taken many of us years to finally get into Extended Events. Once I finally dove into it, I thought to myself "Why did I waste so much time avoiding this?"