Recently we encountered an interesting performance problem on one of our SQL Server databases that process transactions at a serious rate. The transaction table used to capture these transactions became a hot table. As a result, the problem showed up in the application layer. It was an intermittent timeout of the session seeking to post transactions.
This happened because a session would typically “hold on” to the table and cause a series of spurious locks in the database.Read More