Troubleshooting deadlocks in SQL2005

Deadlock in the context of SQL Server means two or more transactions or tasks are waiting on each other to acquire the resources needed to complete. A resource can be a logical lock or can be memory grant or can be a worker thread and so on.   Normally, when a transaction T1 requests a… Read more

Bulk Importing data with OPENROWSET in SQL2005

Before SQL2005, you could use Bulk Insert or BCP to import data into SQL Server. While both of these mechanisms have been popular and used widely, they don’t provide any processing of input data before bulk importing into the target table. If you need to preprocess the data,  you can use DTS (aka SSIS) or, now in SQL2005,… Read more

Range locks

Lately I have seen some quesitons on range locks. Why certain keys are locked? How is this behavior different for unique vs. non-unique indexes? So I played around with range locks and here are my findings so far. Range locks are obtained on index keys to prevent phantoms when you execute transactions under serializable isolation. SQL… Read more

Lock Escalation in SQL2005

Lock Escalation:   If you consider the hierarchy of the objects in a SQL Server instance, at the top level you have the database, followed by schema, tables, table partitions, pages and then finally the individual rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer… Read more