Log in | Register

Waits, Locks and Latches Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education Waits, Locks and Latches Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education

Waits, Locks and Latches


SQL Server 2016 CTP – Alter Column Online Operation

Hi Friends,

Various new features and improvements are coming with SQL Server 2016. Various performance improvements are also added to the CTP versions. Today, I’ll show you one of them i.e. Alter Table … Alter Column online operation.

Blocking is one of the major problems when you work on performance tuning for SQL Server. We knows […]

Hidden Gems of SQL Server 2014 – Managed Lock Priority

Problem Statement:
Theoretically online index rebuild does not block. Practically boom… They do. Online index rebuild operations does not take down your indexes. But there is a point in the index rebuild where they build the new index structure and switch from your old index structure to new one. This is a simple metadata pointer […]

sql server trace flag 1211

Hi Friends,

This is my 19th blog on SQL Server Trace Flag 1211 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

We all know about locks and lock escalation process in SQL Server. Lock escalation take places based on some conditions or you […]

SOS_Scheduler_yield – Wait types of SQL Server

Hi there,

In previous blog we came to know what is wait and what exactly is sos_scheduler_yield wait is, now let us start troubleshooting problems associated around it. The vital step in troubleshooting is identifying the culprit, there are multiple ways out there to troubleshoot but we will do with wait types. I always […]

SOS_Scheduler_yield – Wait types of SQL Server

Hello there,

This is my first blog for SQL server geeks and here I am starting with a series of wait types.

In this series we will unfold different wait types, their meaning and what could be the root cause and possible solutions for it

So before starting let me give this forward /note/ warning :)

Methodologies discussed […]

SQL Server Foreign Key Deadlocks

Recently I came across an interesting SQL Server Foreign Key Deadlock scenario I wasn’t aware off. I didn’t have idea that foreign key constraint can also result in deadlocks.  The detailed steps to replicate the deadlocks are mentioned below.

Test Tables

The Scenario

Step 1: Open a new query window and execute below query under an explicit […]

SQL Server Locks – What are Conversion Locks?

Hi Friends,

This week I posted two articles on Refresher on SQL Server Locks, Part1 and Part2 however realized that conversion lock was left out on both the articles which I should cover immediately part of another post.

In simple words, Conversion locks are resultant of converting one lock type to another. We can have three types of […]

SQL Server Locks – A refresher on Locks : Part -2

Hi Friends,

I hope you enjoyed my first post on A refresher on Locks  Part 1 where I discussed on Shared SQL Server locks, Update locks and Exclusive locks. Today in Part 2, we will cover following lock types with simple examples under READ COMMITTED isolation level;

Schema Locks (Sch)
Intent Locks (I)
Key-range locks

Schema Locks (Sch) : Schema locks […]

SQL Server Locks – A refresher on Locks : Part -1

Hi Friends,

Last week I was in talk with some junior DBA’s and they wanted to understand different types of SQL Server locks with examples. I believe this question is very common for most of the new entrants. Locking is very important feature of any RDBMS which helps us in working in multiuser environments. In […]

SQL Server Deadlock Resolution : Transaction Retry

Transaction retry is one of the easiest way to get control of SQL Server Deadlock Resolution, however it might get tedious depending on number of deadlock an application is experiencing.

The Deadlock occurs because of incompatible locks. The locks are being acquired and released so fast that same locking situation might not exist the other […]