Press "Enter" to skip to content

Category: Extended Events

Tracking xp_cmdshell Executions

Published 2019-06-14 by Kevin Feasel

Jason Brimhall shows how you can see when someone calls xp_cmdshell, including the call details:

What was the wait_type? Well, the obscure wait_type was called PREEMPTIVE_OS_PIPEOPS. What causes this wait? As it turns out, this is a generic wait that is caused by SQL pipe related activities such as xp_cmdshell.

Knowing this much information however does not get us to the root cause of this particular problem for this client. How do we get there? This is another case for Extended Events (XEvents).

Read on for two ways to approach this, both using Extended Events.

Comments closed

Finding High-Resource Queries with Extended Events

Published 2019-06-04 by Kevin Feasel

Grant Fritchey shows how you can create an extended event which identifies high-CPU queries:

A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it’s not readily apparent how to get it.

While you can look at what’s in cache through the DMVs to see the queries there, you don’t get any real history and you don’t get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data.

Click through for the script.

Comments closed

Max Dispatch Latency in Extended Events

Published 2019-05-28 by Kevin Feasel

Dave Bland takes us through the Max_Dispatch_Latency property for an Extended Event:

You would logically think that the minimum setting would be zero seconds.  If you think that way, you are correct.  However, 0 does not mean 0 seconds.  When this is set to 0 it means the event will stay in the buffer until the buffer becomes full.  This is the same set setting the “Unlimited” option you see below. Given this, the true minimum is one second.

Read on to see what it does and why it can be important.

Comments closed

Azure SQL Database and Extended Events

Published 2019-05-22 by Kevin Feasel

Dave Bland shows how to set up and read an extended event file on Azure SQL Database:

This first step when using T-SQL to read Extended Files that are stored in an Azure Storage Account is to create a database credential.  Of course the credential will provide essential security information to connect to the Azure Storage Account.  This first data point you will need is the URL to a blog storage container in you storage account.  If you look below, you can see where you would place your storage account name and the blob storage container name.

Dave gives us the grand tour of the configuration process, including where things differ between on-prem SQL Server and Azure SQL Database (which is quite a bit)

Comments closed

Safely Dropping Extended Event Sessions

Published 2019-04-15 by Kevin Feasel

Denis Gobo shows how you can drop an extended event session without risk of a “does not exist” error:

The other day someone checked in some code and every now and then the build would fail with the error

Msg 15151, Level 16, State 19, Line 51
Cannot drop the event session ‘ProcsExecutions’, because it does not exist or you do not have permission.

I decided to take a look at the code and saw what the problem was. I will recreate the code here and then show you what needs to be changed.  This post will not go into what Extended Events are, you can look that up in the SQL Server  Extended Events documentation

I like these IF NOT EXISTS checks on release scripts as that makes them re-runnable. Even if you don’t use continuous integration for release scripts, you may sometimes hit F5 one too many times.

Comments closed

Tracking Database Recovery with Extended Events

Published 2019-04-11 by Kevin Feasel

Jason Brimhall takes us through the extended events which show progress on database recovery:

Recently, I wrote a rewrite of my database recovery progress report script. That script touches on both the error log and some DMVs along with some fuzzy logic to join the data sets together. That script may not be the most complex script out there, but it is more more complex than using the power of XE.

Database recovery (crash recovery) is a nerve wrenching situation under the wrong conditions. It can be as bad as a root canal and just as necessary to endure that pain at times. When the business is waiting on you waiting on the server to finish crash recovery, you feel nervous at best. If you can be of some use and provide some information back to the business, that anxiety dissipates and the business becomes more calm as well. While the previous script can help you get that information easily enough, I want to introduce the easiest method to capture that information currently available.

Click through for more information, as well as a couple of scripts.

Comments closed

Extended Event Duration Units of Measure

Published 2019-04-11 by Kevin Feasel

Emanuele Meazzo shows how we can find out whether that duration column is milliseconds or microseconds:

Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter.

That’s why I’ve added to Tsql.tech Github repository the following code that extracts the descriptions from XE DMVs in order to identify the unit of measure

Click through for the script as well as the results against Azure SQL Database.

Comments closed

Capturing Execution Plans with Extended Events

Published 2019-04-02 by Kevin Feasel

Pedro Lopes shows us how we can get actual execution plans using Extended Events:

Query execution plans, otherwise known as actual execution plans or just Showplan, provide a map of all the required operations to get the query output, which includes runtime performance statistics.

Query Store (QS) is a valuable tool for troubleshooting workload trends via specific queries – this is because QS aggregates performance data on queries. However, sometimes we need to get the specific, singleton query execution plan to analyze and troubleshoot – this is where xEvents come in.

Since SQL Server 2012 we’ve had the query_post_execution_showplan xEvent for this. As the name suggests, it gets you the actual query plan – because it is *after* execution – when we have the runtime statistics available. However this xEvent is based on the standard query execution statistics profile infrastructure(quite a mouthful) – or standard profiling for short (read more about it here). This has a very high overhead (75%+ with a TPC-C like workload) which is why its use needs to be seriously considered, and most likely not used unless in last resort.

In more recent releases we have other alternatives for these requirements to get the singleton actual execution plans, based on the lightweight query execution statistics profile infrastructure – or lightweight profiling for short (read more about it here). These xEvents are listed below, where we’ll see examples on how to use them.

Pedro does a good job of taking us through the available events and what we get from them.

Comments closed

Finding Where Extended Events Configuration Data Is Stored

Published 2019-04-02 by Kevin Feasel

Dave Bland shows us where we can find configuration data for Extended Events:

In this DMV, obviously there are a number of columns.  This post will focus mostly on the object_type column.  This column has a number of potential values, including Event, Target and Action.  When setting up a SQL Server Extended Event session these are a few of the key items that need to be filled out.

Let’s just take a few moments to identify what these are.

Click through to see where you can find targets, actions, and event descriptions.

Comments closed

Using Extended Events with Azure Data Studio

Published 2019-03-22 by Kevin Feasel

Erin Stellato shows how we can use the Extended Events Profiler (oh, how I dislike that name) with Azure Data Studio:

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.

Erin has a lot of useful information here, so check it out.

Comments closed