A Fine Slice Of SQL Server
Tom 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.
Leave a CommentGrant 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.
Leave a CommentHaripriya 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 closedGrant Fritchey explains a UI oddity:
Let me be extremely clear up front, this is not my original work. I saw this post on DBA.StackExchange.com and I wanted to share and promote it. Nice work FevziKartal.
The rest of this post is just me replicating work already done by others. I just want to see it in action.
Read on for the example and what happens when you don’t have any events in the live data explorer.
Comments closedGrant Fritchey subverts our expectations:
It’s a great question. Let’s say you want to capture stored procedure completions. But, you only want to capture them between 3AM and 4AM. Can you do it?
Read on for the answer, followed by the correct answer.
Comments closed