And there you have it, exact timings for each of the Power Query M queries associated with each of the tables in your dataset. Remember that the time taken by each Power Query M query will include the time taken by any other queries that it references, and it does not seem to be possible to find out the amount of time taken by any individual referenced query in Profiler.
There is a lot more interesting information that can be found in this way: for example, dataset refresh performance is not just related to the performance of the Power Query M queries that are used to load data; time is also needed to build all of the structures inside the dataset by the Vertipaq engine once the data has been returned, and Profiler gives you a lot of information on these operations too.
Check it out if you do any work with Power BI.
If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some good information about various data values within XEvents that are delivered with the Event payload as well as some hidden gems that can be extra useful for exploring SQL Server internals. The point is, maps is not necessarily a new concept.
While the concept of a map is not new within XEvents, the implementation in this case is a tad different. You see, the dm_xe_map_values object is more like an EAV object while the map I will introduce today is more of an ordinary lookup table. Let’s take a look.
The map I want to concentrate on for now is the sys.trace_xe_event_map table. You read that correctly – it is a table. We don’t have very many “tables” when dealing with XEvents, but this one happens to be. As Microsoft Docs describes it, the table “contains one row for each Extended Events event that is mapped to a SQL Trace event class.”
Click through for a script which shows how to map them, as well as a couple interesting points.
XE Profiler looks promising and can be really a great feature. We can use it with no issues on any version of SQL Server which supports extended events – not only with newest SQL Server 2017. I tested it with SQL Server 2014 and it was working well. Currently, lack of configuration of new templates, and logic based on hard-coded names is the biggest concern and discomfort for the user. However Microsoft didn’t officially release yet this version of SQL Server Management Studio, so it’s hard to say what will be the final feature functionality.
I’m hoping that when the final version appears, it will be good enough to get people finally to kick the Profiler habit.
How to set up a recurring Server-side SQL trace that runs every hour for 10 minutes.
6 people in the room are staring at me waiting for the last second request to be done at the end of an 11 hour day (3 of them from the VBV – Very Big Vendor)
Trace file names must be different, or you get errors
Trace files cannot end with a number
I can’t tell time when I am hungry and tired
Extended Events are still the preferred method over server-side traces for getting information, but when a vendor demands traces, the scope for saying “There’s a better way” diminishes quickly, and it’s good to know how to create a server-side trace so you aren’t opening Profiler regularly.
Back in April I wrote a post asking why people tend to avoid Extended Events. Many of you provided feedback, which I greatly appreciated. A common theme in the responses was time. Many people found that creating an event session in Extended Events that was comparable to one they would create in Trace took longer. Ok, I get that…so as a follow up I am interested in knowing how you typically use Profiler and Trace. If you would be willing to share that information, I would be extremely grateful.
Head over there and let her know. For science!
What’s clear from these examples is that trying to relate what’s going on in the query to what you see in Profiler is quite tricky even for seemingly simple queries; for most real-world queries it would be almost impossible to do so with total confidence. That said, when I’m tuning queries I usually comment out large parts of the code to try to isolate problems, thus creating much simpler queries, and I hope the value of this post will lie in you being able to spot similar patterns in Profiler to the ones I show here when you do the same thing. In part 3 of this series I’ll show you some practical examples of how all this information can help you tune your own queries.
Whenever I read Profiler, my next question is “Is there an extended event which covers this?”
Depending on what events you have configured for Profiler, your filter(s), the workload, and how long you run Profiler, you could generate more events than the UI can handle. Therefore, they’ll start buffering to the User TMP location. If you’re not paying attention, you can fill up the C: drive. This can cause applications (including SQL Server) to generate errors or stop working entirely. Not good.
Now, back to the original question. Does the same problem exist for Extended Events? Only if you’re using the Live Data Viewer. After you have an event session created (you can just use system_health for this example), within Management Studio, go to Management | Extended Events | Sessions, select the session and right-click and select Watch Live Data
This is one of those things you hardly think about, but it makes sense: that data’s got to be stored somewhere if things are moving too fast for the app.