Last week one of the attendees on my SSAS cube design and performance tuning course in London told me that he had been prevented from running a Profiler trace on his SSAS Multidimensional instance by a DBA because “he should be using Extended Events”. He wasn’t too pleased, and I can understand why. This, plus the recent discussion about Profiler and Extended Events for the SQL Server relational engine provoked by Erin Stellato’s recent blog post on the subject, made me think it was worth writing a few words on this subject myself.
Microsoft is clear that in the long term, Extended Events (also commonly known as XEvents) are the replacement for Profiler. This page shows Profiler listed as functionality that will not be supported in a future version of SQL Server – although, importantly, it is not deprecated yet. Profiler is still officially supported for SSAS and the SQL Server relational engine in SQL Server 2016. What’s more, in my opinion it will be a long time before anyone doing serious performance tuning work with SSAS will be able to forget about Profiler and use Extended Events exclusively. Let me explain why.
First of all there is the fact that support for Extended Events in SSAS was only introduced with SSAS 2012. If you are using an earlier version you can’t use them. Even if you have some instances of 2012 or 2014 and some on earlier versions the desire to have a consistent set of tools for performance analysis and monitoring means you probably won’t want to use Extended Events yet.
Then there is the fact that in both SSAS 2012 and 2014 there is no user interface for working with Extended Events– instead, to create, start and stop a trace session you have to use XMLA commands. There are plenty of blog posts out there explaining how to do this but it’s still incredibly time-consuming and fiddly to do. Even in SSAS 2016, where there is a user interface for working with Extended Events and viewing their output, it’s pretty awful and nowhere near as good as Profiler (which is far from perfect itself, but at least useable). Perhaps at some point someone in the community will create a user-friendly tool for working with Extended Events, in the same way that the community created DAX Studio to make up for the shocking fact that even in SQL Server 2016 there is no proper support for running DAX queries in SQL Server Management Studio. I would prefer it if Microsoft did the job itself, though, and started taking tooling for BI seriously.
Thirdly, if you want to do anything useful with the .xel files beyond open them up in SQL Server Management Studio, you’re going to need to use some TSQL and functions like sys.fn_xe_file_target_read_file. What happens if you don’t have an instance of the SQL Server relational engine handy though? Most SSAS shops use SQL Server as their data source, but not all – some use Oracle, or Teradata, or other relational databases, and for them installing an instance of SQL Server somewhere just to work with .xel files many not be an option.
Ah, you say, but on the other hand Extended Events have many advantages over Profiler traces: for example, they are much more lightweight! As Adam Saxton says here:
[Extended Events] won’t have the same impact on performance that a traditional Profiler Trace has. For example, it is reported that 20,000 events/sec on a 2ghz CPU with 1GB of RAM takes less than 2% of the CPU.
If I was building a monitoring application (something like, say, SQL Sentry’s excellent Performance Advisor for Analysis Services) then this might be relevant. But 99% of the time I’m not running a Profiler trace on a Production server, I’m working on a dev or test server, and I always try to prevent other people doing stuff on a server while I’m doing tuning work too, so this is irrelevant. It’s a mistake to assume that Analysis Services users use Profiler for the same kinds of thing that SQL Server relational engine users do. For me, I use Profiler to get roughly the same kind of information that a SQL Server developer gets from a query plan: I use it to find out what’s going on in the engine when I run a single query, so the performance overhead is not something I care about.
That said, it certainly seems to be the case that Extended Events will provide more information than I can get from a Profiler trace and allow me to do more things with that data than I can with Profiler. In SSAS 2016 there are several events that are only available via Extended Events and not via Profiler, although I have no idea what they do; I’m sure, with a bit of research, I can find out. Will any of them be useful? I have no idea yet but I suspect a few will be.
Don’t get me wrong, I think Extended Events are a great technology and something all SSAS developers and administrators should learn. There’s still a lot of UI work to do by Microsoft before they are in a position to replace Profiler, but as I said earlier Microsoft hasn’t deprecated Profiler yet so it has given itself a lot of time to do this work. My only problem is with people like the aforementioned DBA who go around telling people they should be using Extended Events with SSAS right now because that’s what they’ve heard is the best practice and that’s what the recommendation is for the SQL Server relational engine. I’ll still need to use Profiler for a few more years yet.
According to this page, profiler has been deprecated: https://msdn.microsoft.com/en-us/library/ms181091(v=sql.110).aspx
There seems to be a lot of confusion around the word “deprecate”…which does *not* mean “unsupported”. A feature can be deprecated but still supported. The link you provided, listing features as not supported in next/future versions is a better (clearer) way to communicate this information.
I’ve certainly been one to favor (even push) xEvents over profiler for SSAS tracing… a lot of that is a) my personal efforts to build familiarity with the (very cumbersome relative to profiler) process of using xEvents w/out a GUI and b) because I’ve been doing quite a bit of workload tuning (instead of isolated query tuning)…so its fair to say profiler has been neglected on my blog for the past few years.
For individual query tunining, I’m totally with you, profiler is the way to go (if not MDX Studio).
PS – really curious about those new “purexevent” events getting exposed in SSAS 2016 (screenshot for reference: https://i.imgur.com/KOWdwcw.png)… should make for a good blog post if you can get the inside scoop from one of you SSAS-contacts 😉
You’re right, that page does say ‘deprecated’ and I’m now confused about what it means exactly too. What’s more, from my SSAS contacts, it seems that Profiler for SSAS is not deprecated yet while Profiler for SQL Server is… but I need to clarify that.
90% of the time I use profiler for quick and easy examination for fellow employees of my company – both dev’s and QA people. I have to go to their box and I don’t have my extended event scripts handy. I don’t have time to mess with the scripts, just want to quickly trouble-shoot a problem. PLUS, I show everyone how to use profiler. And they love it for what it does. No one in dev, QA, or management wants to be forced to learn extended events. This is just another stupid decision ms makes without fully researching the market. ms is insane. period. the day they really take profiler away is the day I retire.
I agree with Bill’s assertion. I find profiler fine for tracking down individual queries or reverse engineering calls to the DB or SSAS. For actual performance monitoring of what’s going on in your production system I think xEvents are a better choice and utilities/code can be built around reading the .xel files to get the information you need.
Hi Chris: As Bill Anton already pointed out, Profiler indeed IS deprecated since SQL Server 2012 already.
But: one has to be a bit more specific. What IS deprecated is:
•SQL Server Profiler forTrace Capture (SQL),
•SQL Server Profilerfür Trace Replay,
•Stored Procs sp_trace_create, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus, fn_trace_geteventinfo, fn_trace_getfilterinfo, fn_trace_getinfo, fn_trace_gettable, sys.traces, sys.trace_events, sys.trace_event_bindings, sys.trace_categories, sys.trace_columns, sys.trace_subclass_values
Profiler for Analysis Services is not deprecated yet. – Usually because something is missing versus Profiler.
Now what deprecation actually means is that you should avoid to use it. But you are still supported.
Wikipedia says: In the process of authoring computer software, its standards or documentation, deprecation is a status applied to software features to indicate that they should be avoided, typically because they have been superseded. Although deprecated features remain in the software, their use may raise warning messages recommending alternative practices, and deprecation may indicate that the feature will be removed in the future. Features are deprecated—rather than immediately removed—in order to provide backward compatibility, and give programmers who have used the feature time to bring their code into compliance with the new standard.
Hope that helps to clarify
I have found that in later version of SQL Server 2012 SP3 — CU4 and greater, that extended events are either getting written out without the domain account or they are only getting written out if the Profiler is running. Has this ever been reported?
I’m not aware of this – so it should be reported, I think. Can you post it to http://connect.microsoft.com/ and then post a link to the issue back here please? I’ll try to make the dev team aware of this once I have the link.
Hi Chris. It’s been a while since you wrote this article :))) I’ve been on the lookout for an xEvents template to troubleshoot slow refreshes of tabular cubes… Would you have any hints for me in this regard, please? TIA.