Extended Events in Azure Data Studio

I was prompted to dig into using Extended Events in Azure Data Studio (ADS) after the latest release came out from the Tools Team this past Monday.  After you download and install ADS, and connect to an instance, you’ll want to navigate to the Extension Manager (blue box in image), then scroll through the list to find SQL Server Profiler (purple box in image):

Azure Data Studio Extension Manager

Azure Data Studio Extension Manager

 

NOTE: Even though the extension is named SQL Server Profiler, it is using Extended Events.  The documentation states explicitly:

“The SQL Server Profiler extension provides a simple SQL Server tracing solution similar to SSMS Profiler except built using XEvents.”

To clarify, the extension in ADS is like XEvent Profiler in Management Studio (which also is built using Extended Events).  The name “SQL Server Profiler” is confusing, as this is not the same tool (UI) that’s been available since SQL Server 7.0.

To install the extension, click on it, and then select Install.  Once it’s installed you can select Reload and it will move into the top half of the window under Enabled.  Notice that when you select the extension, information about how to use it also appears.

SQL Server Profiler Extension Installed

SQL Server Profiler Extension Installed

 

The easiest way to start using Extended Events in ADS is to use Alt+P (for Windows) or Ctrl+Alt+P (for macOS). You can also click on the Manage icon in the bottom left, select Command Palette… and then type Profiler. Using either method, you’ll get a Connection window to connect to the instance:

Connection panel

Connection panel

 

Once you’ve connected, you’ll be prompted to select what type of session to create, and there are three options currently available:

XE New Session

XE New Session

 

This is where things get interesting.

There are three template options:

  • Standard_OnPrem
  • Standard_Azure
  • TSQL_OnPrem

Within XEvent Profiler in SSMS there are only two options:

  • Standard
  • TSQL

The events in the Standard_OnPrem and Standard event sessions are the same.  The events in the TSQL_OnPrem and TSQL event sessions are the same.  The Standard_Azure event session cannot be created for an on-premises installation – it will silently fail.

Once you select the session you want and select Start, a new window opens which is like the Live Data Viewer in SSMS, but with options.  As queries are executing, they will start to appear in the window.  When you have found the information you need, you can stop the event session using the stop button (red box in image, along with Start and Pause) or you can close the window.  If you close the window you will be prompted to stop the event session, which I would recommend doing.  You can also change the view using the Select View drop down (blue box in image), which changes which columns appear in the window.  Lastly, when you have stopped an event session, you can select a different event session which already exists on the instance from the Select Session drop down (purple box in image) and start it from the window.

Session options

Session options

 

A few things worth noting:

  • The Event Sessions created in ADS write to the ring_buffer target, the ones created in SSMS do not write to any target.
  • If you have an event session running:
    • Closing the Profiler window in ADS does prompt you to stop the event session.
    • Closing ADS entirely does not prompt you to stop the event session.
  • You can create the TSQL and Standard event sessions in SSMS, and you can create the Standard_OnPrem and TSQL_OnPrem event sessions in ADS, and theoretically run all of them at the same time.  I wouldn’t recommend this, but if you have multiple users, be aware it’s possible.
  • To see what event sessions are running, you can execute:

SELECT *
FROM sys.dm_xe_sessions;
GO

  • To stop an event session using T-SQL, you can execute:

ALTER EVENT SESSION ADS_Standard_OnPremES
ON SERVER
STATE = STOP;
GO

  • You can still create, alter, and stop event sessions using T-SQL in Azure Data Studio.

Important Query Store Fixes – January 2019

The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade.  As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13.  Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build.  Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs.  I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017.  I’m planning to update this post if the fixes are added down the read.  So here we go, in descending CU order…

SQL Server 2017 CU13FIX: A dump file may be generated when you run the DML internal plan on Query Store enabled database in SQL Server 2017

This will occur for anyone using Automatic Plan Correction (which means you will be on Enterprise Edition), as noted by having FORCE_LAST_GOOD_PLAN enabled.  You can read more about Automatic Plan Correction here (I’m a  fan, it’s pretty cool and very helpful for those with small DBA teams and lots of databases, or those DBAs who just have too much on their plate and are constantly putting out fires).

SQL Server 2017 CU11 and SQL Server 2016 SP2 CU5FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017

I’m aware of multiple companies that have run into this issue and I’m glad to see that the fix was ported back to 2016.  I highly recommend getting up to this CU if you can, as the resolution requires a restart (I’ve heard that killing the session_id also works but I haven’t seen that).  Also note that the command referenced in the KB article is ALTER DATABASE <databasename> SET QUERY_STORE_CLEAR, but I have also seen this when trying to change one of the Query Store options (e.g. changing size or retention days).  I am pretty sure it’s any ALTER DATABASE statement that changes Query Store configuration.

SQL Server 2016 SP2 CU5FIX: Query Store enabled database takes long time on startup after you apply cumulative update for a SQL Server version

Note that this is applicable to systems with Availability Groups, and this is a fix that is not listed for any 2017 CU…I’ll keep watching to see if it shows up.

SQL Server 2016 SP2 CU4FIX: Access violation when SQL Server 2016 tries to start Query Store Manager during startup

This is also a fix that is not listed for any 2017 CU…

SQL Server 2017 CU5FIX: Access violation occurs when Query Store collects runtime statistics in SQL Server 2017

I don’t see this fix in any SQL Server 2016 CU, but I’ll keep watching.

SQL Server 2016 SP2 CU2FIX: Slow performance of SQL Server 2016 when Query Store is enabled

There were many performance-related improvements for Query Store in SQL Server 2017, and huge props to the SQL Server team for getting these back-ported to 2016.  At a bare minimum, this is the build you should be running, but I would rather see you on CU5 at this point.

Note: If you installed CU2 for SQL Server 2017 at any point, please read: Query Store Fix in SQL Server 2017 for an explanation of what you need to do when you apply a higher CU and why.

You may look at this list of fixes and be concerned.  Don’t be.  These fixes are a good thing!  They tell me that more and more people are using Query Store (which is great) and while they have run into issues, I believe it’s because these are bigger systems (see the access violation fix which is for systems with 256 logical cores) or they are interesting/edge-case workloads, both of which probably don’t exist in Azure, and may be why these issues haven’t been seen previously.  I remain, as always, a big proponent of Query Store.  If you’re seeing something interesting feel free to comment here or drop me an email.  I’ve had some folks do that and I was able to confirm they were seeing a bug and let them know that it would be fixed soon.  I’ve had one or two other cases where it’s an issue I haven’t seen and I recommended contacting Product Support.  Either way, if you’re unsure about Query Store I have lots of resources to get you started, to get it configured properly, and I answer the question “what about performance?!”.  I hope this helps!

 

Workload Tuning Training

I recently booked my flight to the U.K. for SQLBits, and I realized I probably should write a post about my full day training session on workload tuning! It’s on Wednesday, February 27, 2019, and you can read the abstract here: Stop Tuning Your Queries and Start Tuning Your Workload.

I included a fair bit of information about what I’ll cover, so I thought it might be interesting to anyone considering this session to hear the backstory about how it came to be.
First, this is a new full day session, I haven’t presented it anywhere else previously. It does have pieces of content I’ve discussed elsewhere, but this really represents a different approach for tuning and troubleshooting. Namely: let’s not focus on one or two or five specific queries that are causing problems, let’s focus on the workload as a whole to identify patterns and then address them in specific queries.

What I have noticed over the past couple years is that I see similar patterns when I’m looking at a given system. Very often, within a solution there are patterns in terms of how queries are written, typical performance problems, and then typical ways they are addressed or fixed internally.

For example, we engage with a customer and discover they use table variables in a lot of their code, and this causes poor cardinality estimates and then a host of subsequent issues. We optimize a couple queries using temporary tables instead, and they then replace all table variables with temporary tables, and then start seeing issues related to temp table contention. Both options have a time and a place, the key is to understand when and why.

The ultimate goal of my session is to share my experience working with a wide range of customers and solutions over the years, the patterns I have seen, and options for addressing them. I’ll tell you now that there is no “easy button” in terms of fixes. But you already knew that 😊 A big key is figuring out how to convince your team of the changes that need to be made, and understanding how and why is critical. That’s what we’ll discuss, because I want you to walk away with applicable information that you can use immediately in your environment.

Still have questions? Add a comment or send me an email. I’d love to hear from you. I hope to see you in Manchester in Feburary!

p.s. I should also mention that next week (Jan 15 – Jan 17) I’m presenting my Query Store course online, and it’s the only delivery of it scheduled for the first part of the year. There is still space if you’re interested! Three afternoons next week, each day with two 1.5 hour sessions. I really like this format, it gives people good breaks to absorb information and think of/ask questions.