Hello. Welcome, everyone and thank you for stepping away for a bit and sharing some of your valuable time with us today. Right, now, it's a beautiful cloudy day here in Boulder, Colorado. Yeah, you may not usually associate cloudy and beautiful, but, it's been really hot and sunny all summer. So, at times like these it's kind of nice to get a little bit of a reprieve from the weather, from the heat. I'm Rob Mandeville, the Product Marketing Manager for Database Performance Analyzer. That's DPA for short. And, I'm joined here by Ash Recksiedler. He is a Solution Engineer here at SolarWinds. And, actually, if anybody was here on the Orion integration episode back in June, Ash had joined me for that one also. So, I'm very glad and maybe a little bit surprised to have him back. [Laughs] Hopefully, I didn't actually scare him off. So that's good.
Couple of housekeeping items before we jump into our topic here. You will be on mute, so if you'd like to communicate with us, you will have to fly or drive out to Colorado and come see us. Oh, or you could just use the chat and QA feature in Web Ex. I guess that would be a little bit easier. Beyond the Basics intent. Why do we do these episodes? I've got reward points with Web Ex, so the more we do here, the more prizes I get. No, but actually, we really hope this helps you get the most value out of DPA as possible. So, that's really the intent here.
Hey, before we get started, I would like to ask one poll question of everybody on the phone. So, I'm going to go ahead and open the poll. The question is, on a scale of one to 10, 10 being the worst, how painful is blocking and/or deadlocks in your database environment? So, I just want some feedback. Is this a big deal for you or not? All right, looks like we've got some really good responses here. Remember, 10 being the worst. So, 10 being very, very painful. One being really not a concern. So, early poll results. Looks like it's probably somewhere in the middle. Right around four and five. So, you know how when you go to a doctor and they say, "Oh, you're feeling pain. Like on a scale of one to 10, how bad is it?" Looks like most are responding right about a four or five. Okay, looks like the poll is wrapping up here in just a few seconds. So, thank you all very much for chiming in on that.
Today is episode four, Blocking and Deadlocks. So, let's talk about that for just a few, though. Notice, I didn't put locking in the title, right? That's because I like to focus on things that are impactful to performance. Now, most, if not all of you, are likely very familiar with databases. You know locking is a good thing. We want locking. Locking is when a session or process wants to read or manipulate data. The lock is required so others sessions won't mess with that same data until the lock has been released.
So, it's really what keeps our data safe from simultaneous manipulation that could cause corruption. Locking allows for read consistency. Now, I don't want to dive into locks specifically. I just wanted to kind of set the level set there. But, just know that there's different kinds of locks. There's different levels of locks. Could be a cable lock, it could be a row or record, or for old-school DBAs out there, a 2PL. Does anybody remember that? A 2PL. There are also different kinds of... The lock can be exclusive or shared. And, diving into the mechanics of the various locking, that's kind of vendor specific. So, we'll stay clear of that in this discussion.
Okay, so if locking is a good and necessary thing, then what's bad? Well, blocking is bad. Right? So, blocking occurs when one session acquires a lock on a resource, like a table or row, and then another session wants to come in and try to access that same resource. But, now it's being blocked by that existing lock. So what happens is that second session just sits and waits until the lock is released and then it can proceed with its work. So, there is another kind of blocking that can occur and it's a very specialized kind of block. It's called a deadlock. And, deadlocks are when you have two sessions or more. Each having a lock on a resource and now they both want access to the resource the other session has locked. So you can see that these two sessions are now at an impasse. That's the deadlock. It's kind of like, I want what you have and you want what I have kind of thing. That's kind of like Ash's kids. No, I'm just kidding. So, in a situation like that, the only path forward, regarding deadlocks, is to kill one of the sessions and then rollback their transaction. By doing that, you release the lock so that the other session can proceed.
Okay, so you're probably thinking, "Great Rob. That's pretty much my understanding of locks, and blocking, and deadlocks as well. So, how does DPA deal with this contention and what insights can it provide for me?" So, great question and here's where I'll turn it over to Ash who's going to show you DPA and how blocking and deadlocks look in our product.
Hey, thanks Rob. And yeah, it's good to be back and no you didn't scare me away [Laughs].
Luckily. No, I'm really glad to be here, because this is a topic, as you know, that's really near and dear to my heart. Let me come answer that question, as far as, how does DPA kind of disseminate that information as far as... First and foremost, is blocking occurring in your environment? But, more importantly, what is the impact of that blocking? And, what's the root cause? DPA just for the record, and you probably already know this but, DPA has always collected your whole entire blocking chain. So your blocker, and then you'll have a waiter. A waiter can also become a blocker. We've always collected this information, but what we've done is we've analyzed all this data for you.
So if you're familiar with DPA, you should be seeing my main screen, the trend view of DPA. I'm not going to focus on the top portion this session. What I'm going to do is, I'm going to scroll down and can I draw your attention to what we call and refer to as correlation tabs. So, this is where you can correlate resource metrics. You can actually look at the SQL text. And then we have these things called blocking and deadlocks. So, this is where in a single pane of glass, not only can we see the activity from a response time perspective with our queries, in the top chart. We can then correlate that activity with queries that not only are waiting, they're waiting for a resource to be released, but we have this concept called Impact of Root Blocking Sessions.
So, as Rob had mentioned, you have an application and you're going to have a connection to the database. Within that connection, which is synonymous with the session, you can have a transaction. Within that transaction, you can have one or many queries, work that's being done by the application to various objects. So, what we've done is we've actually taken the whole entire blocking chain, and what we're going to show you from a 10,000 foot view, is the impact of those root blockers.
Now, to put it into perspective, and I like using analogies just so everyone can understand, this whole concept of Impact of Root Blocking Sessions. You could have a query, and let's make the analogy of someone driving down the highway, and they see an accident on the side, they sort of slow down, they hit their brakes. Well, they're delayed for maybe 10, 20 seconds. But, what you don't realize is how many cars were behind them. So there's this cascading effect. So they were delayed for 20 seconds, but they impacted 10, 20, 30 cars. And, caused them a delay on their journey. This whole concept of impact of the blocking sessions is to look through that whole blocking chain. Look at the person who didn't have anyone in front of them who slammed on their brakes. And, we're going to charge the wait time that they caused all of the other drivers. To me, that's just fair. So what you are seeing here is the actual impact of those sessions that were running.
So, if we highlight these, you can actually see the different queries and the impact. What's also important to disseminate is DPA does a really good job as far as documentation. So what you're going to find is this Learn More. And what we do is we document. So, if you want to know what is the impact of a root blocker, how we calculate this information, and we'll even tell you what this chart can do. Summarize everything. And we'll even tell you how to investigate further. Take full advantage of the Learn More. Now let me go back. Let's just say we got it. The other thing that I want to point out in this particular on July 2nd, is a concept called Idle Blockers. And a lot of people, and I deal with these questions a lot, they want to know what's an Idle Blocker. And you'll notice that in the top tab we say Impact of Top Root Blocking Sessions. We're not saying top SQL statements. That's because blocking, it's not necessarily caused by a query. What we're doing is we're looking at the connection and within that connection you have a transaction, within that transaction you can have queries.
So, if you have a stored procedure, that did some work on an object and maybe there's a cursor, it does an update statement. But, there wasn't an explicit commit rollback. What's going to happen is the work's already done on that object. But, it's now going to cause blocking because it has that object block and it doesn't have it released. And that's going to turn into an idle blocker. And, we also document exactly what an idle blocker is. So, you don't have to take notes as well. So that Learn More comes in very, very handy. So, not only can we see the impact of the root blocking sessions. But, we can actually see who the waiters are as well. So these are the transactions that became blocked. The impact root blocking sessions are the queries that were running up to the point where these queries became blocked. So, these are your victims. And these are your root blockers as well.
What's really fantastic about DPA, so this is the concept of the query from a query perspective, the query dimension. What I love is the fact that DPA is multi-dimensional. So, what we can do is we can actually... If I'm troubleshooting a blocking problem, well, sometimes I want to know what applications are involved. So you see these different dimensions. And, what we can do is we can actually click on, let's say, the Programs tab. Now we can see our top 15 programs that are running, all the response time, and now we change the context of our blocking data to focus on the actual applications. And we can do that with users. Maybe it's a user running a gnarly query. Maybe it's another DBA that's doing something to manage the studio. So, we're able to slice and dice this blocking information by all different types of dimensions.
Now, my favorite dimension, because I'm more focusing on objects. So by clicking on the Plans dimension, and I'd love to see an objects dimension in the future, but plans are going to contain all of the objects associated with that blocking. So not only will we be able to see the actual query itself, but very quickly we can ascertain the object involved. And it can be an index, it can be a table. As Rob mentioned, there's different types of locking at different types of levels. So very quickly, we can assess, Oh, okay. There's one of the primary objects in an update statement. And then what we can do is we can go to the top waiting plans and then we can identify kind of the same object as well. So, if we're coming up with a better schema design, maybe a different indexing strategy, DPA can highlight those objects very, very quickly from a 10,000 foot view. So not only can we ascertain from a 10,000 foot view. Number one, are we having blocking? Number two, how impactful is the blocking? But then you can go deeper as far as, what do we really need to do in order to improve the blocking that's going on in this particular environment?
What's also great is that you have the ability to drill in to these charts. And I'm sure you're familiar with that from a DPA perspective. So now we're looking at July 2, because we have a time machine. So, now we're just going back in time. And if we scroll down, we can click on the top sessions. And actually, what I'm going to do is I'm going to change the context of the data back to the SQL. Scroll down to my correlation tab. Now I can see the impact of my top root blocking session. If I drill in, so this is where we had the most impact, was during the 5 and 6 p.m. time frame. So I'm going to drill in, because I really want to get to the root cause. And you'll notice that during this time period, we had a lot of idle blocking that's going on.
One feature that DPA implemented that I absolutely love, and it'll save you a lot of time especially if you have to determine which portion of the code was causing the blocking, we can use something called find last known activity. So, if I come up here, and go to the blockers tab, you can see our lead blockers, these connections to the database, all the sessions are idle. So now what? Because typically what we want is a blocker. If we clicked on the details, DPA would actually show us all the queries within that connection. So the key to find out what actually caused this blocking is to really identify the last known activity. And it may have been an hour ago, two seconds ago, yesterday. Someone opened up toad [Laughs] and they put a lock on an object and then just walked away. So what we can do is we can click on Find Last Known Activity. And we have something called annotations. So we give you the ability to say, "Hey, I want to annotate."
So we actually found the last known activity that was running in the database up to the point where we had blocking occur. So, this really highlights DPA's session up to the second capability. So, we're monitoring and recording active sessions; queries that are running every second. And so what we did in seconds, in that ten seconds, we took this down to the session, the connections. So if you ever get lost as far as the context of the data that you're looking at, just look at the breadcrumbs. And we took it down right to the one minute mark and annotated. It lets you know that this was the last piece of code that was running prior to the blocking occurring. And then we can identify more than likely, 10 to one, these are the culprits. We can email this to our developers and show them the portion of code, you can do a number of things from this perspective. We can go into View Historical Charts to see the history, the pattern analysis of that particular query. We can analyze that query as well. DPA did this in seconds. Typically, this would take you a week to figure out, to backtrack to determine what was going on. Just a fantastic feature. Okay.
And Ash, the responses from the poll question, it was really indicating really kind of a surprise to me, in how painful blocking and deadlocks can be to a lot of environments. It's really fantastic. It gives you the who, what, when, where, why. Yeah.
Absolutely, I couldn't agree more. That's why it's near and dear to my heart and I really wanted to do this session because this is real world stuff that we're seeing and it can be very impactful to different environments. So, nice to have a tool that really, not only requests all of this information, but that then serves everything up to you on a silver plate. And then...
Yeah. And the picture speaks a thousand words and the one thing about DPA, it almost becomes a report writer, because every screen you go to, you can email and collaborate with management. You can collaborate with other people in your organization.
Speaking of collaboration, just one real quick question. The annotation, is that seen by everybody or just your login?
That is going to be seen by everyone, so it's annotated. And if I go back to the trend view, so that's going to be rolled up. So you can create different annotations. So now you can go back in time and see when that block occurred. Again, now you're seeing everything from a 10,000 foot perspective. And if we correlate with the blocking tab, we can actually see when that happened. And it's documented as well. So annotations, and I want to get on the annotation. [Laughs] Just like that lead driver. [Laughs] Rubber-necker.
But, absolutely the annotation, sometimes it's the simplest features that are the best. Just to disseminate changes and what's going on inside of the environment. And maybe we fixed it, and we can annotate that we actually fixed and what changed. Maybe we added a new index, we modified the query, you can annotate when that change occurred. And then with DPA, you can establish from that point, as a reference, did you improve or degradate performance. Was blocking reduced? Or is blocking increasing? With that new indexing strategy or the new code.
Yeah, so you have the turning information. Now, you alluded to a special type of blocking, which is called deadlocks, that occur. So, you'll see... And one thing I want to point out is that, the deadlocks feature with DPA, DPA is very mindful as far as being performant. Last thing you want to do is be the bottleneck in your environment. But, we pride ourselves on our collection engine. And if we scroll down here to the deadlocks, so when you see that Deadlock tab, and that's for SQL Server 2012 and newer. We're going to create an extended event. And, we leave that up to you to allow DPA to enable it. Very configurable. We'll collect deadlock information every five minutes.
But, what's really nice, similar to the blocking tab, is we have this whole concept of impact; the impact of deadlocks. Deadlocks will occur. Very common. Deadlocks will occur. But, really what you want to know is, "What are the impact of the deadlocks?" Because, when you have a deadlock, you're going to have a survivor and you're going to have the victim. So what happens when that victim is basically a query that was running and gets killed? Well, it has to be rolled back. And typically, there's logic in the application that will retry logic when a deadlock occurs. That work has to be redone. So deadlocks will happen. And we'll actually show you. It's kind of a chart so you can see how many deadlocks are occurring in our environment.
But, what's more important is, what is the impact? So, it's similar to the impact of that root blocker. Here we have some deadlocks that are occurring. And, we're actually taking the time, if I click on this, we're going to present this deadlock information. Personally, I think it's a fantastic view. We're going to give you a summary of the deadlock, when it occurred. All the objects associated with that particular deadlock; the program involved, the actual user. And, like everything else, we have really good documentation that's content sensitive. So we can close this out.
We're going to show you the victim as well. So what we've done is we've parsed the XML, deadlock XML, and we presented it in a really easy to consume report. We'll show you the SPID, the deadlock priority. We'll also show you the survivor. And we're also going to tell you the type of lock it is. And document what that lock type is. And why this is important is because a lot of times deadlocks can be treated by using the same techniques that we would apply to blocking. But, it really depends on the type of lock that we're dealing with in some cases. It's really code related, which means, again we can collaborate with the development team and send them all this information.
If you're used to looking at things from a management studio perspective, you can download the deadlock XML as a XDL and then open up and manage it in studio and then you get the nice graph as well. So let's just scroll back up. One thing I do want to highlight, again it's along the lines of help, if I go back. And we document as well with Learn More, strategies for resolving deadlocks. Really nice article that describes what deadlocks are. Finding deadlocks, and what's really important is at the bottom kind of this is that XML that you are used to seeing. And if we scroll down to the bottom, we sort of describe DPA and how it deals with deadlocks as well at the bottom. It's a really good article. So even after this session, you can go to our online database.demo.solarwinds.com, the Learn More will be there. Or, within your environment as well. Just know there's a lot of great information that can be disseminated as far as blocking and deadlocks.
I told Rob that this would be a pretty fast session. [Laughs] I don't want to use up every single minute. Your time is valuable. But I think, for the most part, I'm going to wrap it up here. DPA has a fantastic way of, very quickly, from a 10,000 foot view, showing you whether or not you have blocking. It also will allow you to dig in, go deep, from a granular perspective to find the root cause in seconds. So hopefully, you enjoyed just this quick presentation, quick demo, of how DPA kind of will show and reveal deadlock issues or blocking problems in your environment. With that I'll pass it back over to Rob.
Ash as always, thank you very, very much. That was fantastic.
All right, thank you.
Yeah, yeah. No, thank you. Just to kind of wrap things up here a little bit, we've had a couple of questions in the Q&A section. One was, does DPA have the blocking and deadlock information or the same screens as DPA for SQL? And, the answer is yes. So, Oracle will have the blocking information. Deadlocks are auto-resolved by Oracle, and Oracle doesn't really collect specific information about deadlocks like SQL Server does in the extended event. So, you will have all the blocking information. Deadlocks are really just a specific kind of blocking scenario. So, with Oracle as you can see, we do trap that information. It says, another question, are deadlocks also auto-resolved in SQL Server? And, again, the answer is yes. SQL Server will take care of it.
The analysis that we do really, really dives into the victims and survivors kind of question. And, that kind of speaks to the overall impact of what deadlocks are doing in your environment. Another question. How would you capture sessions causing lock timeouts versus blocking? So our application tool sets the timeout to five seconds. Okay, so we're going to see it from a database perspective. So, as that session comes into the database to do whatever transaction it was instructed to do for that request, that's fine. You will see it in here as a blocker. And, after five seconds... Or a 'blockee,' let's say. Because if it's set out to like, block timeout at five seconds, the second session that comes in the trench to acquire a lock on that same resource, you will see him as a blocked session. But, probably only for five seconds. Because at that point, the application is going to go ahead and say, forget it, timeout, we'll try again. Right?
So here, as you can see Ash is kind of expanding the blocking tree. You would see session 289. And, probably that would say I had a timeout value of six seconds. So after that it says, I'm out. So that would disappear from our radar as a non-active session the next time we do a quick pull, which is the very next second.
Okay. Let's see. Another question. I don't have the deadlock tab. Is there an option I need to have enabled? I also don't have annotate or view last activity. I would ask what version of DPA you have installed. Barron, do you mind chiming in on that. You should have it. I think... Ash, do you remember when deadlocks was? Was that 10.2?
10.2 is when it was released.
Okay. And Barron says he's on 11.03.78.
What version of the SQL Server? Because it's only for, the deadlocks is only for 2012 and newer. So if he has 2008, he's not going to see the deadlocks tab. But he'll see the blocking tab and the annotations as well.
Yeah. That should be there. So I'm kind of surprised. Barron, if you continue to have questions about that, I would definitely recommend opening a support case. And then they can take a look at your environment and see why that's not showing for you. Okay, cool. Thanks. Another question. He mentioned the deadlock tab is only displayed for SQL Server 2012, I thought? Yeah. That's correct.
Okay. So I think that's about it for the Q&A section. I don't believe anything went to chat. Let me make total sure on that. Yeah, I think we're clean from a chat perspective. So I think that wraps it up. If anybody has any questions or comments or anything, go ahead and shoot us an email after the fact and we'll go ahead and communicate that way. So with that, I'm going to wrap it up. It looks like we're one or two minutes shy of top of the hour. Once again, Ash, thank you so much for helping out today and walking us through this feature, this blocking and deadlocks. It was fantastic.
Yeah. No, thank you. For all of you on the phone, thank you very much for taking some of your valuable time and spending it with us. All right, until next time. If anybody's interested, we're doing another one of these episodes in August. I believe it's going to be on the 22nd. So, if you got this one, you should get a follow-up email saying thanks for attending and stuff. And, I believe, there's going to be a link to go to our website on THWACK and go ahead and sign up for the next episode. All right, I hope everybody has a great rest of the day. Thanks all.