I'm upgrading an old system from Lock AllPages. It had a few deadlocks recently. The system inserts records slowly, one by one, outside transactions, so I suspect the deadlocks were due to Index pages.
I made a few test with the "new" locking schemes. It appears the system works best if I convert the larger tables (2 million rows) at Lock DataPages, while smaller tables (40K rows) convert to Lock DataRows. When I tried DataRows on the large tables as well, the system slowed down by 20%, especially at the overnight functions which must execute a few table scans. This all makes sense.
There was a lot of discussion about the merits of row-level locking. I have heard less about page-level locking without index locks (perhaps I was just asleep).
What is the experience with Lock DataPAGES ? Any issues to watch for, when moving from Lock AllPages ?