Azure Data Studio – Profiler

For many years DBAs have been using Profiler to help with troubleshooting issues.   This involved opening a second application name SQL Server Profiler.  With Azure Data Studio we can now stay in one application by utilizing the SQL Server Profiler extension. Like most extensions, this one is still in preview and could change before final release.

The documentation states this, “The SQL Server Profiler extension provides a simple SQL Server tracing solution similar to SSMS Profiler except built using XEvents.”.  This means that the SQL Server Profiler extension is using Extended Events instead of trace.

As stated earlier this is an extension, therefore will need to be installed.  This is a pretty easy process.  The first thing we need to do is to open the extensions window.  This can be done under the View menu.

View –> Extensions

Once the extension window opens, do a search on “Profiler”.  When you do you will see this.

Once you find it, click the Install button you see above.  This extension was created by Microsoft and is a very fast install.  In addition, if you look at the above image, you will see a star in the upper left corner, this means that the extension is recommended by Azure Data Studio.  You will not need to restart Azure Data Studio once the installation is complete.

The shortcut of the Profiler extension is ALT + p.  If you use the short cut before you have installed the extension, you will still see something that appears to be profiler even though it is not.  The image below is a screenshot.  Notice that the tab says Profiler, yet the sessions are all the Extended Events sessions that are on the server I am connected to, so this really isn’t Profiler, even though it might look like it. Just a side note, if you have the proper permissions, you can start and stop Extended Event sessions here.

Once the Profiler Extension is installed there are two ways to open it.  The first one and the easiest one is to use the shortcut, ALT + p.  The second method is to utilize the Command Palett.  This can be found under the View menu, should be the top item. When you open it, type the word “profiler” in the search and it will appear as below.

Once you open Profiler, if you do not have an active connection, you will be asked to create one.  Then you will be taken to the screen below, which is the same screen you will be taken to if you have an active connection.

You will not be able to pick the events like you can in Profiler.  At this point in time, it appears that you are limited to two profiles, TQL_OnPrem and Standard_OnPrem.  Notice both say OnPrem, there is a third option when you connect to an Azure SQL Databases, Standard_Azure.  Although there are three, you will only see the Standard_Azure template when connected to an Azure SQL Database.

Earlier I mentioned that the Profiler is built on Extended Events.  This is how you can see if a current session is running.  Extended Events information is stored in a number of system tables and views.  To see what Extended Events sessions are currently running you can use the code below.

SELECT *
FROM sys.dm_xe_sessions;

Your results will look similar to what is below.

Once I start the Profiler trace, I will see an additional session.  Notice that the name starts with ADS.  This will be the name you will see if you are using the TSQL Templete, ADS_TSQL_OnPrem.  Once you stop the trace, this entry will disappear.

Once the session starts you will see this across the top of the grid.

Going left to right, let’s briefly cover each item

Select Session:  This has a drop down box to the right.  When a session is running, this drop down box is not available.  However, if you stop the session, you will be able to pick a new sessions.  The options include the two mentioned above as well as any Extended Event sessions that are currently on the server

Start\Stop\Pause:  These do exactly as you would think. Start a sessions, stop as session and pause a session

Filter\Clear Filter: This allows you to place a filter on the results.  Here you can also save the filer, however I was struggling to get this to work properly.

Select View:  Like the session label mentioned above, this also has a drop box to the right.  However, for this, the items in the drop down list simply change what is displayed.  When changing this, you will not only see a change the data that get’s captured, you will also see changes in the columns.

Autoscroll: This turns of the autoscroll

Clear Data:  This is the farthest one to the right.  Like a few of the other’s, this does exactly what it says it does, clears data.

Once you start collecting data, you will see a grid.  If you look next to the EventClass header you will see a little green arrow.  This is supposted to sort the data in a manny you decide.  What I have found it that this works on some columns, while appearing that is doesn’t work on others.

If you select a line in the grid, you will then see the Text and Details tabs in the bottom half.  Similar to this screenshot.  The text will simply return the query that was running, like below.

The Details tab will look similar to below. It will display the data for a the record that is selected in the grid.

There, now you are ready to use the Profiler extension.  One of the limitations is that you do not have an option of where to save it.  Just like the XEvent profiler in SQL Server Management Studio, the data here is somewhat temporary.

Earlier, I mentioned filters on the Profiler.  While they do exist and they are easy to work with, I would be a good idea to know where the filter stored.  The answer, is this filter is storted in the settings.json for Azure Data Studio. To see this we need to review the json file.  The settings window can be opened by using the Preferences menu item under File.  When you do a search for the word “Profiler” you will get the results as you can see below.

All the settings will require you work with the settings.json file.  To get there simply click the link. In the image below, you can see that there is a setting that was saved by using the save functionality in the filters form.  In this case the name is default, the field I want to filter it on is SPID and looking for a value equal to 51.  For the operator, 0 is the value for equal, while the value for not equal to is 1.

You now have enough information to start using the Profiler extension.  Over all, it isn’t bad, however because this is a function of a client application caution must be used.  If there are a number of folks in your environment that have Azure Data Studio installed, you could have a number of folks running this at the same time.  Given that the Profiler Extension is based on Extended Events, you should be aware that for the most part Extended Events are very lightweight, however if many people are doing this, there is a risk of causing a performance issue.  So use…but use with a certain amount of caution.

Thank you for visiting my blog!!

 

 

 

 

 

 

 

 

 

 

 

XEvent Profiler

XEvent Profiler is not a SQL Server feature, it is a feature of SQL Server Management Studio(SSMS). It was first introduced in SSMS v17.3 and the XEvent Profiler sits on top of Extended Events technology.  Per Microsoft, XEvents “enables quick access to a live streaming view of diagnostics events on the SQL server”.

You can find XEvent Profiler at the bottom of the server tree in SSMS, just under SQL Server Agent. There are two sessions that come with XEvents, Standard and TSQL.

 

Starting and Stopping the Sessions

When you first Launch the session and the Extended Event sessions does not exist, SQL Server will create the session, however it not be started.  To start it you need to right click on the session in XEvent Profiler and click “Launch Session”.  If the Extended Events session already exists, SQL Server will start the session.

When you are done doing what you need to do, simple follow the same steps and click “Stop Session”.

Standard Session

When you enable this session it creates an Extended Events session named QuickSessionStandard.  This sessions does not have a target defined and has a Maximum Dispatch Latency of 5 seconds.  If you create an Extended Events session the default Maximum Dispatch Latency is 30 seconds, significantly higher than the Standard session. This session collects a lot of performance information.  However, it also collects login and logout information. Below you will find the list of events that are being collected.

One thing you might notice about the above image is that there are number Predicates defined.  These are all the same, they exclude system processes.

When you launch the session, SSMS will allow you to watch live events.  The screenshot below is what you will see when viewing live events.  Among the information collected, you will find Database Name, Batch SQL, Client Application, User account and the query_hash.  This view can be modified to meet your needs. The changes to the view will remain when you close the session.

TSQL Session

The second session available is the TSQL session.  The Extended Event session is named QuickSessionTSQL.  As the name implies, this session collects information about the execution of TSQL statements. The events are below.  You notice a few things about these events, like the QuickSessionStandard session, the TSQL session does not have a designated target.

  1. Many are also in the QuickSessionStandard session including:
    • Existing_connection
    • Login
    • Logout
    • sql_batch_starting
  2. There are fewer events than in the QuickSessionStandard session
  3. The QuickSessionStandard session has the sql_batch_completed while this does not

Like the QuickSessionStandard session, the events in this session also have a few predicates applied. All of which are to exclude system sessions from the live data.

Viewing Session Data

When you launch either of these sessions, SSMS will also launch the screen that will allow you to view live data, see below.  Something to keep in mind, when you close this window, it will stop the session.

Modifying the Sessions

Although you can modify the view, it may not be the best idea to modify the definition of these sessions. If you find a need to modify these sessions, it might be better to either modify another user defined Extended Event session or create a new session.

Both of these sessions are intended to provide a quick way to collect and view some basic performance information about your SQL Server.  For those of you that like to use Profiler, this could be a good alternative, even though it is not as powerful or very flexible.  Since neither of these sessions has a defined target, the data is not stored.  Once you disable the session the data is gone.  If you really need this data for review at a later day, you can export it.  The export menu can be found under the Extended Events Menu item.

Thank for stopping by!!!

Additional Information

Microsoft

MSSQLTIPS