Jason Brimhall looks at an error when trying to set up an Extended Events session on Linux:
This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to the Database directory after creating it while in sudo mode. I will get to that in just a bit. Let’s see what the errors would look like for now.
Msg 22048, Level 16, State 1, Line 15
xp_create_subdir() returned error 5, ‘Access is denied.’
Msg 25602, Level 17, State 23, Line 36
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: ‘Access is denied.
‘ while creating the file ‘C:\Database\XE\PREEMPTIVE_OS_PIPEOPS_0_132072025269680000.xel’
Read on for the solution.
Jason Brimhall shows how you can use Extended Events to figure out if that database is still in use:
Here we can see there are indeed some databases that are still in use on this server. If we are looking to retire the instance, or migrate relevant databases to a new server, we have better information about how to go about planning that work. Based on this data, I would be able to retire the ProtossZealot database but would be required to do something with the ZergRush and BroodWar databases. In the case of the ProtossZealot database that is not being used, we now have evidence to present back to the team that the database is not used. It is now up to those insisting on keeping it around to justify its existence and document why it must remain in tact.
Click through for the script Jason used to determine this.
Dave Bland ran into an interesting problem during a demo:
Recently during a demo at a SQL Saturday the query to pull the Extended Event session data, didn’t return the expected results. The session I used for the demo was the create database statement.
Prior to the session, I deleted the Create Database session, however did not delete the target files because they are part of the demo. Then I recreated the session, just as I had done before. However, this time was there was a difference when I attempted to read the target data. The entry for the newly created database was not showing up when I used the GUI, however was showing up when I read the XML. During the session, I was not able to figure out why that was the case.
Click through to see the root cause and how Dave fixed the problem.
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.
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.
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.
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)
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.
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.
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.
Kevin Feasel
2019-07-11
Extended Events, Linux