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.
Jason Brimhall has some tips to help you use the file target in Extended Events:
This first little tip comes from a painful experience. It is common sense to only try and create files in a directory that exists, but sometimes that directory has to be different on different systems. Then comes a little copy and paste of the last code used that worked. You think you are golden but forgot that one little tweak for the directory to be used. Oops.
Read on to see how SQL Server exposes that error, and then Jason shows us a different how-not-to with file targets.
Jason Brimhall shows how you can create an extended event to track whenever databases go offline:
The other day, I shared an article showing how to audit database offline events via the default trace. Today, I will show an easier method to both audit and monitor for offline events. What is the difference between audit and monitor? It largely depends on your implementation, but I generally consider an audit as something you do after the fact. Monitor is a little more proactive.
Hopefully, a database being taken offline is a known event and not a surprise. Occasionally there are gremlins, in the form of users with too many permissions, that tend to do very strange things to databases and database servers.
Click through for the script.
Arun Sirpal shows us the extended event to watch to learn who changed that auto-tuning setting:
It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning)
- CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
- DROP INDEX – identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that at this time the option is not compatible with applications using partition switching and index hints.
- FORCE LAST GOOD PLAN – identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.
Personally I don’t enable the option where it is allowed a “free-for-all” when creating/dropping indexes and forcing certain query plans. I like controlling the change, especially for production databases. To force this concept I wanted to use Extended Events to know when / if someone changed my settings for automatic tuning against my database.
Click through for the script.
You’ll note that the second statement in the sequence is “CREATE OR AL…” in the batch_text. That’s me modifying the procedure. The very next event is sp_cache_remove. It shows the remove_method as “Compplan Remove”. This the plan being removed in an automated way from cache. The next three events are all for query_cache_removal_statistics.
What are they?
These are the statement level statistical information being removed from the DMVs. That’s right, we can observe that information getting removed from the system along with the plan from cache.
Unless I’m missing something, it seems like this is more helpful for pedagogical reasons rather than auditing reasons—I’d be concerned that on a busy production system, we’d see too many messages to correlate things all that well.
Matthew McGiffen shows off some of the things you can do easily with Extended Events Profiler:
When I’m using Profiler to analyse performance issues I often save the results to a table, or upload a trace file into a table, so that I can analyse the data. Often this involves aggregating the values for particular queries so that I can see the most resource hungry.
This is by no means a difficult process, but with Extended Events (XE) it’s arguably even easier.
Click through for a demonstration.
Grant Fritchey shows how easy it is to build an extended event which captures implicit conversions:
Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here’s one example of how you might capture implicit conversions along with the query:
This kind of event can make system-level performance tuning much easier.
Kevin Feasel
2019-03-22
Azure Data Studio, Extended Events
No Comment