Extended Events and SQL Server Profiler

Question

I understood the concept of Extended Events and SQL Server Profiler, I felt like both of them do the same work. I could not understand the major difference between them. Can anyone explain me the major difference between Extended Events and SQL Server Profiler? And when can we use in production environment? Is SQL Server Profiler preferable for production servers?

Answer 1

Profiler uses under the hood an outdatated and already deprecated technology, called SQL Trace. It isn't updated anymore to be possible to monitor newer engine functionality, e.g. memory optimized tables. So Extended Events has many more events that it can monitor than SQL Profiler:

It also has much more flexible outputs (targets). In addition to "classic" targets like file and ring buffer (where full information about the events is saved in "tabular" format), there are also event counter and histogram targets. They can help you achieve even lower overhead, because they simply counts how many times particular event occurred, without the overhead of saving the collected data. There is also pair_matching target, which could help you relate events to each other, like start and end of transaction for example.

Another advantage of XE over Trace is that the definition of a trace is not human readable (at least not easily readable):

While XE definitions are more clear for normal people:

Trace still can be used, bu Extended events is the recommended way to monitor your production servers.