Paul White (12/14/2009)

---

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx (5000 held locks, approximately)

TheSQLGuru (12/14/2009)

---

Assuming good cardinality then there could be 5000 or so PAGES locked if the engine decided that page locks were appropriate (which is likely for any reasonable rows estimated that don't automatically trigger a table lock). That is many more rows than 5000 stated as the break point. Good number, just variable thingy locked.

TheSQLGuru (12/14/2009)

---

But again we are back to the query that does the work and the estimates the optimizer works with and finally decides for the actual INSERT and especially DELETE queries. OP?? Any followup here??

billmcknigh (12/14/2009)

---

Have read the paper SQL Server 2005 Waits and Queues, which I guess is a locus classicus of the waits and queues methodology. The delete statement, BTW, is a DELETE FROM table WHERE ID IN (SELECT TOP 3000 ID FROM table). In themselves, the deletes are very fast. The Inserts are ordinary row level (fired by a trigger) inserts, but 1000 per minute are arriving continuously Finally, should mention that we had to add memory to our system because SS was wanting to use more than we had, so some significant sluggishness was due to having the server page to disk..