Notes on SQL

Random articles from a puzzled DBA

I know, I know – I should be using Extended Events instead of Profiler, but I still have a soft spot for Profiler. Profiler is deprecated and in theory could vanish with little warning – it certainly isn’t having the enhancements that Extended Events is getting.
One of these enhancements has just arrived in SSMS 17.3 and has provided further temptation to abandon Profiler – an easy to launch XEvent Profiler interface, with basic functionality.

The latest version of Management Studio can be downloaded here. Having installed it, there is a new group ‘XE Profiler’ at the bottom of the screen:

The new XEvent Profiler

For basic traces you can either double-click on ‘Standard’ which will show all Extended Events in this session or ‘T-SQL’ to view logged SQL statements. Note that the session can take a little while to start displaying the results.

The ‘T-SQL’ option gives me a quick way of tracing SQL code running on the server:

XEvent Profiler for T-SQL

Right-click on the column headings and you can remove columns, add from a range of other columns or search through the columns. You can copy the value of a cell, a row or the entire results and paste them into Excel (or suchlike) for more detailed manipulation. Copying the whole results will bring back the data for all columns – whether you selected them within XEvent Profiler or not.

Searching is available within a specified column and the entire text data can then be seen in a separate window by double-clicking on the ‘batch_text’ in the results pane, which can then be copied to the clipboard.

Filtering on a value within a column is also available and works when you right-click on the column of interest.

Possible Issues
If you need to change the column that you’ve filtered on then it would appear to be easier to cancel that filter and right-click on the new column that you wish to filter against. Attempting to change the column via the ‘filters’ dialog doesn’t work correctly on my setup – the list of columns briefly appears in the top left corner of my screen.
Similar fun can be had attempting to change the ‘Operator’ value.
In addition, when selecting the additional columns ‘Database Name’ always appears to be NULL, although ‘Database ID’ appears to work well.

Database ID with values and NULL name

I like it. It allows me to start a basic trace quickly and easily. In the development environment this will help the developers with a minimal amount of fuss.
However, the issues I’m seeing with it are a tad irritating. As with many of Microsoft’s releases, the first version of a new feature has some issues that really shouldn’t be there.

And yes, I will be taking a more detailed look at Extended Events.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: