The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time -

i have large table (70 million records) heavily indexed (5 indexes) , need merge around 100 000 records (several times in row). merge , merges ok , return

the instance of sql server database engine cannot obtain lock resource @ time. rerun statement when there fewer active users. ask database administrator check lock , memory configuration instance, or check long-running transactions.

the merge has no transactions around , called (as stored procedure) entity framework (v 4.0) without transactions.

using (var b = new myentities()) {     b.commandtimeout = 36000;     b.merge_my_stuff(); } 

also server has 16 gb of ram (most of taken sql server), data in table 2.5 gb , indexes 7 gb. i've checked memory constraints ,

max server memory (mb)  2147483647  2147483647  maximum size of server memory (mb) min server memory (mb)  0   16  minimum size of server memory (mb) 

any advice appreciated. why on earth sql server need many locks merge?

edit merge statement (execution plans i'll add later)

merge target_table target using (select * i_tmp) source on target.a_id = source.a_id ,    target.b_id = source.b_id ,    isnull(target.c_id, 0) = isnull(source.c_id, 0) when not matched target     insert(a_id, b_id, c_id, d, e)     values(source.a_id, source.b_id, source.c_id, d, e) when matched     update     set d= source.d,         e= source.e;  delete i_tmp 

looks lock timeout error. it's unrelated server memory, , instead caused contention other threads.

here couple things might help:

1. ensure index exists of merge conditions (a_id, b_id, c_id) keys (even better if clustered index has these keys)
2. reduce batch size significantly

if unable reduce batch size, consider using tablock/tablockx hint in merge statement. due large batch size, sql escalating locks table locks anyway, increases transaction time. going directly table locks should mitigate timeouts.