Good morning. My name is Steven Stout. I am one of the pre-sales consultants at IDERA. Today we are going to look at capturing deadlocks within the IDERA SQL Diagnostic Manager tool. And how it displays and captures them. The first thing really to do is to make sure that we are actually capturing that information. And if you come within the IDERA SQL Diagnostic Manager dashboard you get to see on the right-hand side the various instances. Check in to those. Then we can come into the properties. And this will actually allow us to make sure that we are collecting that. The deadlock information for this particular instance. This is the general properties page. You will see in a number of situations. But the actual deadlock information is switched on here on the activities monitor. So once you have actually configured that. And you have got the system running. You will then see if you are looking at the sessions information. Those deadlocks being recorded within here. And you can see the pink line going up and down where the logs are being recorded. Actually, in the background down here I have got some background process. And letter actually creating deadlocks for us to look. Yet okay. So now looking at this. Sure we can see that steaming. We can see the deadlocks coming through […]. Do some more drill down. And understand what is going on with them. So as it is typical with SQL Diagnostic Manager. You can find multiple ways to navigate through the tool. That it does not matter which way you choose. These ways probably just to click directly on the […]. Let us take us into the sessions information. You can see on the top right by the way that it is actually navigated in the variety of ways. Put into the sessions. So looking in the sessions. We get the standard sort of sessions information. So how responds to the server is looking at the select one from master response time type of information. Another sort of background information on the sessions. When we specifically now are looking at the dead logs versus again. Clicking here and go to look at the dead log information coming through. So we are now on the blocking page that the sessions are going to see various bits of information being coming. In the background here. This is all real-time. We get to see the blocking reports. But for the purposes of what we are looking at today. This is also where I was he the deadlock reports right. Just double-click on here. I can actually go and bring up a deadlock report listener to give me a lot more information about what was involved in that deadlock embrace. So I can see the two spits are actually involved in the deadlock embrace. And the speeds of the […] slightly graphical representation. Here is the spit that was that was killed. The victim that rolled back clicking on it. Then chose me down here more information about what that is big particular was doing. So I can see that for example. He was the user Katia. We can see the host. Who is in the process were coming in from the SQL commands. Which database etc and so forth. And even down here to the nature of the SQL that was being issued. I can, by the way, see that SQL as well. If I hover up here over the SQL stage. But probably most usefully is the fact that I can export this data out in XTL format. XTL is one of Microsoft’s XML [extensible markup language] extension formats. But the great usefulness is that you can then give this file directly to the developers. And they can then open it in SQL Server Studio. And it will immediately show them which objects were actually involved in that deadlock embrace. Because of course deadlocks are caused by the objects being locked in an inconsistent order. And first a way to address that is to understand which objects are actually involved. Thank you.
- Whitepaper: Troubleshooting Locking with DMVs
- Solution Brief: Find and Fix SQL Server Deadlocks
Topics : Database Monitoring,Database Performance,
Products : SQL Diagnostic Manager for SQL Server,