Tom Collins has a new Extended Events session for us:
Track memory grants with this Extent Event
Click through for the event session and a sample of how to query the resulting events.
Leave a CommentCategory: Extended Events
Tom Collins has another extended events session for us:
A SQL Server Extended Event to track SQL queries taking longer than 100 seconds to complete. Adjust accoring to your requriements.
There is also a query below to extract the column details from the xel file
Click through for the code. This kind of extended events session is rather useful for performance tuning and finding issues before customers e-mail.
Leave a CommentTom Collins has a pair of scripts:
Step 1 : Create the SQL Server Extended Event and output to and event file
Step2 : Excecute this query to read the output of the xel files generated by the Extended Events Session .
There’s not a lot of commentary around these, but the session includes blocked processes and deadlocks, so that’s nice if you have intermittent blocking chain issues.
Leave a CommentGrant Fritchey knows one way to solve the problem:
A client asked us to tell them when a query ran long. Simple. We have a long running query alert, all built in to Redgate Monitor, so, done. No, see, we like getting alerted when queries run long, but not really long, plus we’re more concerned with just one database.
Click through for the story and how Grant was able to help out the client. Also, read the comments for an entry by Special Guest Star Erik Darling.
Comments closedGrant Fritchey makes a choice:
For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don’t just think that’s a good thing. I think it’s a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I’m trying to do? Except for blogs like this one, there’s not always a lot of guidance on these things. Microsoft has (good) documentation on Extended Events in general, but not on every event, plus, not a ton of guidance. (some though).
Click through for Grant’s advice.
Comments closedTom Collins wants to know what’s going wrong:
We have an ETL job failing every night . The ETL job is using SQL Server Stored Procedures , so we’re looking to be trace the errors reported in SQL Server, to give some clues on how to fix the problem?
Do you have an approach to monitor and save SQL Server error messages ?
This is an extremely useful way of catching problems in code, as well as people goofing up ad hoc queries.
Comments closedGrant Fritchey learns a thing:
I live for questions and this was an interesting one. Can you see Table Valued Parameters that have been passed in to Extended Events? I literally have no idea. I’m sure we’ll see something, I just don’t know what. Time to find out.
Click through for the answer.
Comments closedHaripriya Naidu creates an Extended Events session:
If you’re a DBA, how many times have developers asked you to check whether a particular stored procedure is hitting SQL Server?
Yes, we’ve all been there. Typically, you either run Profiler or set up an Extended Events session to trace it. But the question is, have you ever been confused about which event action to use?
Read on for the list of candidates and when you should use each.
I do mildly disagree with Haripriya’s last note around running Extended Events in production. There’s a small performance hit for most events. There are a few events that do have a much stronger impact, but for the large majority of events, you can (and probably should) have them running to provide the relevant diagnostic information. This isn’t like Profiler or even a server-side trace, where there’s a fairly significant overhead cost.
Comments closedSlava Murygin uses Integration Services to process an Analysis Services cube:
If you process SSAS cubes via SSIS packages you might notice a weird message like “Full Processing:Warning: Server: Operation completed with XXX problems logged.“
How you can get that message (if you have that problem):
Slava notes that the simple solution doesn’t actually work, as both services decide not to store that information. But Slava does provide a solution to the problem.
Comments closedI haven’t had a rant post in a while. There is a saying: “Anything before the word ‘but’ is ignored”. I love Extended Events, but … reading the extended event file is so much pain.
It feels like there is a conspiracy between Microsoft and Big
PharmaSQL Monitoring because the best analytics tool available in SQL Server (and I mean Extended Events and Query Store of course) have the worst GUI and supporting tools. I’m focusing on XE in this post.
Read on for Tom’s rant. To add on to it, the feedback item Tom links to now has a new update from Dimitri Furman, indicating (based on how I’m reading it) that the fix will be in SQL vNext, not SQL Server 2022.
4 Comments