How to find blocking queries in sql server

this is part 86 of sequel server tutorial in this video we'll discuss how to find a blocking queries in sequel server blocking occurs if there are open transactions let's understand this with an example here we have two instances of sequence of management studio on the Left we have a transaction I'm going to execute only part of this transaction notice I have not executed the commit statement that means we have an open transaction here and on the right we have a few queries that touch the same table that this transaction on the Left is updating now let's see what's going to happen if we try to select the count from the table notice the query is naturally blocked and that's because we have an open transaction now let's cancel this query and try to delete that row from the table and look at that the same block now let's try to truncate the table and finally try to drop the table so all these queries are affected by this open transaction all of them are blocked now let's see what's going to happen when we complete the transaction by executing the commit statement at this point if we try to select the count from the table we should not have any problem basically all of these queries should succeed so the obvious next question that comes to our mind is how to find the blocking queries for that we will have to find the open transactions now in this case it's easy to find the open transaction because we have started this transaction but in real world there will be multiple users connected to the database and we don't know what queries they're executing and what transactions they have left to open now if we try to execute queries and if they are blocked then the most likely cause for that might be you know an open transaction so we are definitely interested in finding out open transaction one way to find open transactions is by using sequel server dbcc open Tran command the problem with this command is that it will only display the oldest active transaction it's not going to list all the open transactions let's look at that in action so at the moment we don't have any open transaction so when we execute dbcc open Tran it should say no active open transactions now let's execute part of this transaction and now let's execute dbcc open channel look at that oldest active transaction and it gives us the process ID that has executed the transaction and the process ID is 54 and if you look at this window here notice the process ID here is 54 so this has got that oldest active transaction now let's try to select count of star from table B so now we get the data without any block but let's try to execute another transaction on that table so here you know in another window I have a transaction let's only execute part of this transaction so we have another open transaction at this point if I try to select count of star from table B that will be blocked and then to find the open transaction if I use DBC see open Tran so let's execute cancel this and execute dbcc open Tran so it gives information about process ID 54 and if you look at process ID 54 it's updating table a and I'm trying to get the count from table B so here the oldest active transaction is not the transaction that's blocking my query so it's some other open transaction but you know that's not the oldest open transaction so dbcc will not be able to provide information about that now the obvious next question that comes to our mind is is it possible to list all the open active transactions absolutely on the web on sequel skills com I have found the script which is extremely useful and provides valuable information about open transactions so let's copy that and execute that right here so let's maximize this window and let's execute this query so now look at that it gives a lot more information about those open transactions first of all it gives us the session IDs 54 and 56 and it gives us the login name associated with that processes so we can use that login ID and then contact that person and ask them to complete the transaction right and we also have the transaction begin time and the beauty is we also have the actual sequel script the transaction is executing look at that process 54 is executing in are basically this sequel which is updating table a so process 54 as you can see is updating table a and process 56 which is this one here is updating table B and if you look at your query here we are trying to select count of star from table B and if you look at process 56 is the one that's affecting your query so you will contact this person and ask him to complete in order transaction now if the person is not available you know you also have the option to kill the process but killing the process may have unintended consequences so use this option with extreme caution now the obvious next question that comes to our mind is how to kill the process again there are two ways to do that one is by using sequel server Activity Monitor and to get to the Activity Monitor within the object Explorer right-click on the server instance and then select Activity Monitor so this is going to display this Activity Monitor vendor expand the processes so it should list all the sequel server processes and the processor that we are interested in is 56 that's what we want to kill and to kill it from the active witty monitor go to that process right click on that and you have the option to kill that process so let's kill it now when we go back and execute this query it should succeed without any problem look at that the process is killed now let's run this query once again so now we only have one active transaction and that is with process ID 54 now one way to kill the process is by using Activity Monitor the other way by is by using sequel command kill and then we specify the process ID so let's go ahead and do that for process ID 54 so I'm going to use kill and the process ID is 54 execute that command completed successfully and when we execute this query now we should get no open transactions so what actually happens when we kill a session all the work that the transaction has done will be rolled back basically the transaction is ruled back that means the database must be put back in the state it was in before the transaction started that's why it is rolling back that transaction when we kill a session thank you for listening and have a great day

Channel: kudvenkat

Views: 86,029

Rating: 4.875 out of 5

Keywords: sql server find blocking processes, sql server list blocking processes, dbcc opentran example, sql server kill spid, sql server kill connections, sql server kill query, sql server kill transaction

Id: BSiQi1b5VZY

Length: 7min 35sec (455 seconds)

Published: Thu Sep 03 2015