I hate SQL Profiler. It’s clunky, slow, hard to read and filter, and it can suck resources away from the Server you are trying to monitor.
However, Extended Events also seemed a little daunting at first but there are other people out there that have taken one for the team and have created some great little (!) Extended Event Sessions. More importantly, Extended Events is very lightweight compared to SQL Profiler so as a DBA obsessed with keeping performance at an optimum, it’s time to forget about profiler. In fact, I never include profiler in any of my SQL builds. That way there is less temptation to use it.
2 EE sessions I use and set up on my production environments (although I don’t run these continuously, only when I need information or i’m troubleshooting) are both by Jeremiah Peschka.
The first one is for blocked processes and deadlocks
The second one is for detailed workload performance analysis and can be used for looking at query performance metrics and waits as well as metrics around reads, writes, duration, and CPU time.
Profiler has been deprecated for a long time and I wish Microsoft will now do the decent thing and finally put it out of its misery.
Extended Events is already 10 years old and although it wasn’t pretty back in 2008, it has come on in leaps and bounds and should be your go to tool for SQL monitoring especially for those who can’t afford a third-party monitoring tool.