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.
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.
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.
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.
Erin Stellato explains how to use the XEvent Profiler in SSMS 17.3 and later:
It’s worth pointing out that neither the Standard or TSQL session writes out to a file. In fact, there’s no target for either event session (if you didn’t know that you can create an event session without a target, now you know). If you want to save this data for further analysis, you need to do one of the following:
1. Stop the data feed and save the output to a file via the Extended Events menu (Export to | XEL File…)
2. Stop the data feed and save the output to a table in a database via the Extended Events menu (Export to | Table…)
3. Alter the event session and add the event_file as a target.
Read the whole thing.
Dave Mason shows us how you can use T-SQL to shred XML coming from extended events sessions:
Querying the data of an Extended Events session has never been easy. My XEvent sessions typically store event data in a target file, which means using sys.fn_xe_file_target_read_file. To get something of value, you need to shred the event data XML.
Doing this in T-SQL isn’t great. It’s probably better to shred in another language—F# would probably be my choice due to its type provider—and dump the results back into SQL. But if you want to stick to one language, Dave shows you how.
Grant Fritchey loves Entity Framework:
Yes, Entity Framework will improve your job quality and reduce stress in your life.
With one caveat, it gets used correctly.
That’s the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell.
One nit that I’ve always had with Entity Framework is that it’s very difficult to tell what part of the code the call was coming from. You really have no idea. So when my friend, Chris Woodruff, asked me on Twitter what would be the best way to monitor TagWith queries in Entity Framework, well, first, I had to go look up what TagWith was, then I got real excited, because, hey, here’s a solution.
That “I love Entity Framework” is the lead-in to a one-act play of mine with people with pitchforks, tar, and feathers. Nevertheless, Grant shows us how we can tag code in C# and capture that data in extended events. I’d read it but I’m too busy sharpening my pitchfork.
Kevin Feasel
2019-05-22
Administration, Cloud, Extended Events, Storage
No Comment