Performance and concurrency go hand-in-hand.  In fact, these are more closely tied that many DBAs and developers realize.  I’ll underscore the important relationship between performance and concurrency in this last article of my concurrency series and explain why performance tuning can fix blocking problems in addition to improving response times. Why Performance Affects Concurrency A bad query execution plan touches much more data than necessary and also consumes inordinate CPU and disk resources.  The result is that more locks are acquired than needed and these are held for longer durations when concurrent queries complete for conflicting locks, CPU and disk resources.  This...

In the first post of this series, I reviewed concurrency concepts and highlighted differences in concurrency terminology from a database and application perspective.  I’ll now discuss how applications can leverage SQL Server features to meet concurrency objectives. SQL Server Concurrency Terminology Optimistic concurrency control inside the SQL Server 2005 database engine technically means that row versioning is used instead of a pessimistic locking approach.  I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control.  Applications have used optimistic concurrency long before row versioning was introduced...