In the top pane I can now right-click on object_name and select Group by this Column. Right-click on object_name, which will highlight it, and select Show Column in Table. The object_name is displayed in the top part of the viewer, but clicking on any rpc_completed event shows all elements captured in the Details pane. Grouping is easier here, because I know the stored procedure name. If I were looking for a specific stored procedure that was slow, I could either scroll down until I find it (which could be painful), or I could group or filter the data. Now I can see all the events IвЂ™ve captured in order of highest duration to lowest. The first time I click it will sort in ascending order, and because IвЂ™m lazy and donвЂ™t want to scroll the bottom, IвЂ™ll click again to sort in descending order. Now that my queries are no longer rolling by in a blur, I can click on the duration column to sort my events. Stopping the Data Feed in the Live Viewer To perform any kind of analysis, the data feed must be stopped. In order to find such a parameter, we need to sort the data based on duration, which we cannot do when the data feed is active. In a case where there is a stored procedure which has large variations in performance, capturing the input parameter can be extremely useful as we can match up an execution that takes longer with a specific value passed in to the stored procedure. One of the benefits of using rpc_completed and sql_batch_completed is that the input parameter shows up. If we start up the Standard session and run some queries, in the viewer we see the event, the query text, and other useful information like the cpu_time, logical_reads, and duration. For both sessions, the only filter is to exclude system queries for the batch, rpc, and attention events. If youвЂ™re looking to understand information about query execution, such as how long it took the query to run, or how much I/O it consumed, then Standard is a better option due to the two completed events. HereвЂ™s a listing of the events for each: Standard TSQL The Standard and TSQL sessions share some events, with the Standard having more in total. Live Data Viewer (after double-clicking to start Standard session)

Assuming you have activity against the server, you should start to have entries show up in the viewer in five seconds or less.

Note that after you start the session, it will also appear under Management | Extended Events | Sessions. Behind the scenes, an internally defined event session is created (if it doesnвЂ™t already exist) and started, and the Live Data Viewer immediately opens with focus. To use either one just double-click on the name. There are two options for XEvent Profiler: Standard and TSQL.

And one of the quickest ways to get started is to use XEvent Profiler, which is available through SQL Server Management Studio (starting in version 17.3): If you want to understand query performance for individual executions to pinpoint the exact query or set of queries which might be a problem, the easiest option is to use Extended Events.

There are of course 3rd party monitoring solutions that are more than capable of giving you all of this and more (like SentryOne), but for this post I wanted to focus on native tools. Query Store also provides average metrics for numerous resources, but itвЂ™s aggregated over a defined window of time (e.g. for a query only for as long as itвЂ™s been in cache. The DMVs represent average CPU, I/O, duration, etc. You can use the DMVs or Query Store to get information about query performance, but the information in both sources is an aggregate. In the course of monitoring performance or troubleshooting a problem such as system slowness, it may be necessary to find or capture queries that have high duration, high CPU, or generate significant I/O during execution.

Sql server profiler 17