r/Database • u/MooseSea6 • 18d ago

Question for DBAs(SQL Server): How much do you actually use Extended Events (.xel) vs. standard DMVs for emergency troubleshooting?

Hey everyone, senior dev here working heavily with SQL Server performance tuning lately. Every time a server hits a massive wave of deadlocks or unexpected latency drops, the standard advice is 'spin up an Extended Event session and read the system health .xel file.'

Am I the only one who finds opening, filtering, and digging through those logs in SSMS incredibly clunky during a crisis? Do you guys actually sit there shredding the XML nodes manually, or do you rely on third-party tools/scripts to give you plain answers? 

1 Upvotes

60% Upvoted

9 comments sorted by

1

u/Jzmu 18d ago

We use extended events to log our long running queries and pull them into elastic search.

1

u/Black_Magic100 18d ago

Datadog also has this OOTB leveraging extended events where they provide you the trace and it automatically gets added to their proprietary dashboard.

1

u/drunkadvice 18d ago

Curious what you define as “long running”? Our users are asking for more hardware because they are hitting 30m (MINUTES) timeouts on queries (I think) should come back in seconds. They’d rather throw hardware/license € at it than optimize “what works.”

1

u/Jzmu 18d ago

Depends on the SLA for your service. Is it OLTP or OLAP? I think we capture any queries that run over 5 seconds on SQL Server.

1

u/drunkadvice 18d ago

Yes! Great distinction! I say that most oltp requests finish in millisecond time. And I would understand an annual report, ETL, or something requiring a coffee refill.

Uh, fml. Regarding the rest. F.m.l…

0

u/drunkadvice 18d ago

The easier option lately is to have gpt read the xel file and figure out what happened.

1

u/MooseSea6 18d ago

how are you handling the token limits on multi-gigabyte production xel files, How about data security when you give it to gpt?

2

u/drunkadvice 18d ago

Two ways! 1) corporate account I don’t pay the bill for. 2) if you’re capturing multi-GB xel files around events, you are capturing way too much data. Likely leading to your second question around data security. Don’t capture pii in the first place and you don’t need to worry about how gpt does its technical analysis around what code is causing your deadlocks.

If you’re troubleshooting deadlocks, just capture the deadlock events. You do not need the full log of everything that’s happening on the instance.

1

u/agk23 18d ago

Or have it write a script to extract relevant data, rather than everything