Follow a few of the SQL Family on Twitter and you’ll mostly see one view regards SQL Profiler, and it’s not generally friendly.
So much so that I’ve been tempted to buy this t-shirt for some trolling at the next SQL conference I attend:
At SQLBits earlier this year, Erin Stellato ran a great session “Kicking and Screaming: Replacing Profiler with Extended Events”. Erin’s run it a few times at various conferences and you can check it out on YouTube:
https://www.youtube.com/watch?v=HnqLGJuRgvw
As a stubborn continuing user of Profiler I attended because I liked the kicking and screaming bit, being willing to accept that might fairly accurately describe my stance.
Erin ended the session asking for a commitment from the audience that next time they used profiler – just put aside 15 minutes to try seeing what it’s like to use extended events instead.
I’d used Extended Events (XE) on occasion, but generally when it was something that couldn’t be done in Profiler. I also regularly make use of the built-in system_health Extended Events session, particularly for retrieving details of deadlocks.
But if I needed a quick trace for something – to profiler I go. In my defence, I’m not a production DBA, so I’m checking things on dev and test instances where performance isn’t critical. But even where performance is a concern you can define the trace in profiler and then export that to implement as a server-side trace where the performance impact is minimal.
As a side story here, profiler can slow things down as it needs to write events back to the app before each event can complete. A Microsoft consultant shared a story with me where they’d been called out to troubleshoot a client who was experiencing high CPU on the database server. The MS guy started a quick trace to see what was going on, and as that slowed queries down it actually reduced the CPU. The manager ran out yelling “What have you done – it’s fixed!” There had to be a fair amount of convincing to persuade the manager that leaving profiler running permanently wasn’t a solution to the CPU problems.
Back to the subject of the post. There are a couple of reasons why you may still want to use profiler (apart from where you’re running seriously old versions of SQL):
- When you work with clients, it’s a technology they’re all familiar with. I can ask a client to run a trace and send me the results. I don’t have to explain in detail what to do so it’s easy to get what I need. If I asked for the results of an XE session I’d generally get the verbal equivalent of head scratching – they’ve heard of it (probably) but wouldn’t know how to go about it. They may also wish to go through a change process to implement one as they don’t understand well what the impact might be.
- I want something I can replay. You can’t do this with extended events, but you can if you take the right sort of trace. Moving forward you can use “Distributed Replay” for this, but I don’t know anyone who’s done that.
- You’re using Amazon Relational Database Services – with RDS you have restricted rights over your database instance, and the ability to create extended events sessions is something you don’t have access to.
There’s also a couple of reasons people say you shouldn’t use traces that I disagree with:
- Performance. I’ve already mentioned this. Yes, XE is more lightweight, but if you implement your trace as a server side one then the impact is small.
- Profiler is deprecated. Well, yes it is, but only from SQL 2016. The ability to not have to terminate your SQL statements with a semicolon has been deprecated since 2005 and we can still do that. And as Hugo Korenelius informs me, there are features that have been deprecated even longer and are still with us. Trust me, trace will be with us for a good while. I’ll happily take a wager on that if you don’t believe me. I might even wager that I’ll be retired before Profiler is.
However, since making the pledge at Erin’s session I forced myself to try Extended Events for some of the more basic tasks I would have turned to Profiler for and I feel there are some reasons people (myself included) haven’t been using XE that are no longer valid:
- It’s quicker/easier to set up a profiler trace. That was true in the early days of XE, but arguably now (depending on what you want to do) you can have your XE session going with remarkably few clicks.
- XE produces XML output. I hate XML, I don’t want to start using XQuery to find what I want. Trust me, we all hate XML. With the GUI we now have for XE you don’t have to mess with that stuff.
- With XE I can’t save my results to a table. Um, yes you can. A table, a csv file (with no XML I promise) or just copy and paste to Excel. And unlike profiler you can search the data without having to export it anywhere.
So yes, I believe that there is still a place for profiler, but no longer because it is quicker, simpler and more usable.
In subsequent posts I plan to show easy and convenient XE is to work with these days.
And yeah, okay it is more powerful, more lightweight, allows me to look at things profiler never could. People have been telling me that for years and it wasn’t enough to make me change – but “easier”? Give me that thing!
Still probably going to buy that t-shirt though…
Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.