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.
Phil Factor gives us a great walkthrough of Extended Events:
A lot of the information about the way that SQL Server is working that can only be provided by Extended Events (XEvents). It is such a versatile system that it can provide a lot more than would otherwise require SQL Trace. If done well, Extended Events are so economical to use that it takes very little CPU. For many tasks, it requires less effort on the part of the user than SQL Trace.
Extended Events (XEvents) aren’t particularly easy to use, and nothing that involves having to use XML is likely to be intuitive: In fact, many DBAs compare XML unfavourably in terms of its friendliness with a cornered rat. SSMS’s user-interface for Extended Events, thankfully, removes a lot of the bite. For the beginner to XEvents with a background in SQL, it is probably best to collect a few useful ‘recipes’ and grow from that point. Templates and snippets are invaluable.
Phil’s workbenches (especially those written with Robyn Page) are fantastic ways of digging into a topic of interest.
Grant Fritchey shows how to make the Extended Events UI a lot more useful:
WHAT!!!!?!!
You mean I can make that upper window useful? Yes. Not only that, but, what ever columns you add to the upper window, your copy of Management Studio remembers what you did. The next time you open this Extended Events session in the the Live Data window, the columns you selected will be there. However, your ability to customize Live Data is even easier than that. If you notice, in the upper part of your SSMS window is the Live Data tool bar. Near the end is a button innocuously titled “Choose Columns.” Click on that and a window similar to this will open:
And you can share the results with others.
Kevin Feasel
2019-02-11
Extended Events, T-SQL
No Comment