Concurrency Series: Common Concurrency Issues with Applications

As part of my role as a program manager in SQL Server Engine, one of the areas I am reponsible for is the concurrency related features in SQL Server. If you have been involved with database applications as a develeoper or as a DBA in keeping the application running, I am sure you will agree that blocking is one of the most… Read more

Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency

Another improved functionality in SQL Server 2008 R2 I am posting this on behalf of Juergen Thomas who has been with SQL Server PM team from 12+ years and is an expert in SAP.  Juergen>> In this article I’d like to talk about another improvement we made to SQL Server 2008 R2. The improvement pretty… Read more

Overhead of Row Versioning

Last week I was teaching a class on snapshot isolation and we discussed the overheads of snapshot isolation. There are three main overheads that you need to be aware of when you enable snapshot isolation read-committed-snapshot on a database. First, each UPDATE/DELETE operation generates a row version. Note, that the row version is not generated when you INSERT a new row but… Read more

Read-Only filegroups and Locking

I recently saw a newsgroup post that sort of implied that accessing data residing on read-only filegroups can save you on locking. Well, it could, but it does not. If you really want to save on locks, the whole database needs to be set to read-only.   Back to read-only filegroups. They provide you the… Read more

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