Situation Your query is waiting for another query and the other query is doing nothing. Blocking Locks A blocking lock occurs when one lock causes another process to wait until the current process is entirely done with the resources. Deadlocks Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions. Lock-Avoiding Design Strategies There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks: Use clustered indexes on high-usage tables. Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time. Break long transactions up into many shorter transactions. With SQL Server, you can use “bound connections” to control the execution sequence of the shorter transactions. Make sure that UPDATE and DELETE statements use an existing index. If you use nested transactions, be sure there are no commit or rollback conflicts. Problem What most users do is, they kill the query that is blocking your query. Ofcourse this is very bad practice but we also understand that it is not an uncommon thing to do. It would be better to look at your indexes, optimize the resource usage of your query and to automatically avoid this situation from happening. Kill Blocking Locks and Deadlocks The following script can be automated via SQLServer Agent Jobs or executed manually to help you kill connections: [crayon-5be2f27802672964738214/] Permissions if @showonly = 0 ALTER ANY CONNECTION (inherited via sysadmin or processadmin) and VIEW SERVER STATE (for sp_lock and sys.dm_os_waiting_tasks)   If @showonly = 1 then NO connections will be killed. Support In case you are in need of SQL Server support on this subject or others, you can contact us at SQLTreeo via online chat or e-mail servicedesk@sqltreeo.com. We deliver 24x7x365 managed services and support.

The post Blocking Locks and Deadlocks appeared first on SQLTreeo.