Locking and Blocking

A recent issue we faced was SQL Server blocking by a session that had a negative SPID value. We tried to kill this session, but were not able. In this tip we look at how to kill sessions with negative SPIDs.

We have a customized database index maintenance job that runs every night, however from time to time the index maintenance job will block other business jobs or sessions and can cause business problems. In this tip we look at how to setup a process to det

There is a stored procedure that must be run by only one user at a time. How do to I prevent multiple users from running the stored procedure at the same time? In this tip we look at using sp_getapplock and sp_releaseapplock.

Under heavy contention your transactions could be the victim of a deadlock and therefore be rolled back. In this tip I will show you how to implement retry logic to re-run the failed transaction.

Occasionally I come across UPDATE and DELETE statements where the target table is referenced with the NOLOCK hint. Does this hint help or hurt performance in this case? In this tip we cover whether this hint is useful or if it should be removed from your

Recently, I noticed this intriguing message in one of my SQL Server 2008 R2 error logs: Warning: Failure to calculate super-latch promotion threshold. In this tip we review locking and latching and this is used by SQL Server.

You are looking at ways of ensuring atomicity and consistency in your database by ensuring transactional integrity. You may want to avoid locks, or ensure problems such as lost updates or dirty reads cannot occur, or otherwise ensure the quality of your d

In my previous article (Different techniques to identify blocking in SQL Server), I discussed locks and blocks, and presented you with an overview on how to troubleshoot and resolve blocks using dynamic management views and Activity Monitor. After I wrote

At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the

Learn about the different ways to capture and identify blocking that is occurring in SQL Server.

Learn about locking and blocking in SQL Server and why and how it occurs.

Help! My SQL Server database is locking. That can't be good! What should I do?

Learn different ways to execute SQL Server SELECT statements without locking and causing blocking issues.

Using the default SQL Server READ COMMITTED isolation level, my application sometimes falls victim to the dreaded lost update condition where two of my users edit the same row for update but the user who submits his/her change last overwrites changes ma

One way to get around SQL Server blocking is by using different indexes to cover the queries that may be running, learn how in this article.

A common problem in many environments is locking and blocking. Locking and blocking can cause performance problems and a poor user experience. If this problem worsens, it can be escalated to deadlocking. At a high level, these two problems are due to m

Learn how to improve processing time when using data queues by using SQL Server READPAST and UPDLOCK hints to eliminate blocking and other issues.

In your recent tips on SQL Server dead locks (How To: Graphical Deadlock Chain and Deadlock Priority Configuration) I can see the value of using Profiler to capture the process related information. Do any other options exist in Profiler to capture the in

I have read your tip on SQL Server locking, blocking and deadlocking (Finding and troubleshooting SQL Server deadlocks) which are applicable to some of issues I have been recently facing. I have been noticed locking on my SQL Server and have heard about

In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If

One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL S