A few weeks ago Kevin Kline gave a talk in Dublin about SQL Server performance and how to make the most of it. The talk was very interesting because Kevin touched a few times on SQL Server internals. The most surprising one was related to how SQL Server escalates locks. Kevin mentioned that if SQL Server has acquired around 4000 locks within a table then it escalates them into a table level lock. What is even more more surprising is the fact that this value is hardcoded. I've tried a few time to prevent SQL Server from escalating locks and I've always failed. Now I know why :)