A Fine Slice Of SQL Server
Grant 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 CommentsLori Brown’s speaking my language:
Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records. I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.
When checking for blocked process records, you must set the blocked process threshold in the configuration. I have mine configured with the threshold set to 30 seconds. This means that blocked process reports are generated every 30 seconds.
Click through for that configuration setting, as well as the Extended Events session to do the work, and even code to pull the results into a table. It doesn’t get much simpler than that.
Comments closedGrant Fritchey shows off an interesting use case for Extended Events:
Another discussion in Gothenburg (such a great group of people) around Extended Events lead to a (admittedly, tiny) idea for an additional use for sp_statement_completed.
The basics for sp_statement_completed are pretty straight forward. If you want to capture a procedure’s behavior, you use rpc_completed. If you want to know about the individual statements within the procedure, you use sp_statement_completed. Now, I don’t recommend turning this on across the board or without some good filtering in place because, it’s likely to generate quite a bit of data. However, it can be useful, including in the following manner.
Click through for a silly but clarifying example from Grant.
Comments closedGrant Fritchey answers a question:
I had an excellent group of people in Gothenburg Sweden when I taught there and I was asked: Can You See Who Forced a Plan?
I didn’t know the answer for certain, so I said what I always say: I don’t know, but I’ll see if I can find out.
Read on for one false start but then the solution of how you can do it.
Comments closed