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. 🙂
Oh yes absolutely. I just posted the link to illustrate where the 5000 number came from.
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??
With any luck he's reading about waits and queues or hiring a consultant 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi