Does disabling lock escalation put more stress on the data mdf?

Question

We have a large multi-use application suffering from OBJECT and PAGE blocks on a number of tables. We are unable to reconsider the design, but need to reduce blocks that affect performance on client machines. I have been considering disabling lock escalations on one table but need to know what pressure it will put on other resources. The disk i/o is already strained. Will additional, individual locks require more i/o than the automatic table locks? Will it affect our system databases more than our application db? We don't do full table updates/reads. Each request will only deal with a very small portion of the table.

Answer 1

We have a large multi-use application suffering from OBJECT and PAGE blocks on a number of tables.

...

The disk i/o is already strained. Will additional, individual locks require more i/o than the automatic table locks?

You misunderstand lock escalation, this is clear from the parts of your question that I made bold.

Lock escalation goes from rows to table or from pages to table (I excluded partition as it's not your case), so if now you have page locks it's NOT lock escalation.

Lock granularity is choosen by server unless you use hints (rowlock, paglock) and if it choses page locks there is no escalation. If it then removes all the page locks and substitutes them with table lock, it means lock escalation occured.

The second thing that is wrong is your thinking that locks have to do with IO. This is not true. Locks are held in memory and have nothing to do with reads. You can check this article to see how CPU usage and query duration increase when the locks are more granular: Why ROWLOCK Hints Can Make Queries Slower and Blocking Worse in SQL Server.

You should understand what causes your lock escalation.

Lock Escalation Thresholds

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

• A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

Lock Escalation (Database Engine)

So if you reach 5,000 locks per statement threshold you should split your operations to smaller batches.

And if you are under memory pressure, disabling lock escalation will make your situation even worse.

UPDATE

I've found this description of locks in the book Microsoft SQL Server 2012 Internals (Developer Reference)by Kalen Delaney (Author),‎ Bob Beauchemin (Author),‎ Conor Cunningham (Author),‎ Jonathan Kehayias (Author),‎ Paul S. Randal (Author),‎ Benjamin Nevarez (Author

Locks aren’t on-disk structures. You won’t find a lock field directly on a data page or a table header, and the metadata that keeps track of locks is never written to disk. Locks are internal memory structures: They consume part of the memory used for SQL Server. A lock is identified by lock resource, which is a description of the resource that’s locked (a row, index key, page, or table). To keep track of the database, the type of lock, and the information describing the locked resource, each lock requires 64 bytes of memory on a 32-bit system and 128 bytes of memory on a 64-bit system. This 64-byte or 128-byte structure is called a lock block. ... The lock manager maintains a lock hash table. Lock resources, contained within a lock block, are hashed to determine a target hash slot in the hash table. All lock blocks that hash to the same slot are chained together from one entry in the hash table. Each lock block contains a 15-byte field that describes the locked resource. The lock block also contains pointers to lists of lock owner blocks. Each of the three states has a separate list for lock owners.

Hope it helps.