drawbacks of disabling lock escalation

Question

I have a table called MyFactTable that looks like this:

Create table MyFactTable
(
RunID int,
Key2 int,
Key3 int,
Key4 int,
….
Value2 numeric,
Value3 numeric,
Value4 numeric,
….
)

It also has:

• A Non-unique clustered index on RunID (non-unique because a RunID can have hundreds of thousands of rows associated with it);
• A handful of other non-clustered indexes to help querying;

Run IDs are completely isolated from each other and normally I’ll have more than one process inserting into this table (with different RunIDs obviously). The problem is that I don’t seem to be able to run inserts in parallel, meaning that when process A is inserting into the table, process B is blocked from also doing so.

That is not a big deal in 95% of the cases as the most of the RunIDs are very small (less than 500 thousand rows) and they will lock the table for a few seconds only, but eventually a bigger job (20+ Millon rows) starts and it locks the table for a few minutes, preventing all the smaller processes from completing.

I imagine this is happening because the first process that tries to insert into the table is acquiring a TABLE LOCK, so I disabled lock escalation with this command:

ALTER TABLE MyFactTable  SET (LOCK_ESCALATION=DISABLE)

That did solve the problem, but now I’m wondering what the consequences of doing that may be.

Has anyone faced a scenario like this and would be willing to share their experience.

On a second note, what other possible solutions could I go with? I thought about partitioning the table and putting all the small runs in one partition and all the big runs in another (I don’t care if the big ones block themselves, I just want to be sure the small ones don’t get blocked by the big ones). What do you think about that?

I guess it is worth saying that this table is never updated and once a RunID is inserted, it is only queried (so no more inserts with the same RunID) and eventually it is deleted by a clean-up process.

Thanks, Diego

Answer 1

Summary:

For Inserts/Selects i don't see any issue with disabling lock escalation ,except for some resource usage

Normally Inserts won't lock the table,unless you are doing bulk loading or insert select ..

When you disable lock Escalation,other than using more memory,i don't see any issue with Inserts ,Except for more Memory usage ,Memory used will be

Lock uses 96 Bytes of memory in SQLServer,So when table holds row lock for each row,the memory used is proportional to number of Rows *96 Bytes

Now coming to transactional consistency of other transactions like insert,Update/Delete on the same table,when Lock_escalation is disabled..

There will be no transactional inconsistency ,since they will be just blocked ,if the locks are not compatible

Finally ,i see an issue in performance for other DML transactions .See below example for more details

Table1 has 1000 pages and each page has 100 rows,totalling 100000 rows.now When you insert data into this table .Now Each row will be locked With 'X' lock until the transaction has completed.so a Delete/Update has to check Each row to know whether it can hold lock or not..Earlier,it can check lock at higher level

I am not sure whether Delete/Update , will be blocked immediately when it first finds a row with incompatible lock or it will continue checking as i i don't have test 2008 instance to test currently.will post more details