Query in a batch takes from few millisec to 2 minutes on SQL Server

Question

I have a batch that loads configuration data parsed from about a hundred XLS workbooks, with information on cells position, type of cells, meaning of cells etc.

This is a very large batch, it involves use of several temporary tables,

Since it can start with an existing configuration, it need to merge the two configurations, so some temporary table are filled out-of-transaction, some are filled in-transaction.

On Oracle 10g the batch executes without any problem.

On SQLServer 2008 R2 I'm experiencing some random hangs on 2-3 in-transaction INSERT queries. This thing doesn't always happen, or maybe it happens on a query (that normally executes in few millisecs), but not on another on which it happened the time before.

I initially thought about a deadlock, but raising query timeout to 3 minutes, it finally executes in about 2 minutes. I repeat, these queries sometime executes in few millisecs.

I also specify that all queries are done with option for ROWLOCK only.

Monitoring SQL Server with profiler and activity monitor I don't see anything strange.
CPU is not hanged, there is memory, disk reads are about 0, disk writes are 0-200 kb/s not constant.

No one other is using this DB schema.

I really cannot work it out how to solve this.

EDIT:

This is one of the puzzling queries

INSERT INTO "LOAD"."META_CELLS_UOM" WITH (ROWLOCK) 
("UDA_DOMAIN_ID", "META_DOC_ID", "META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_UROW", "META_CELL_UCOL", "CEM_ID") 

  SELECT "UDA_DOMAIN_ID", "META_DOC_ID", "META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_UROW", "META_CELL_UCOL", "CEM_ID"
   FROM ( 
     SELECT "META_DOCUMENTS"."UDA_DOMAIN_ID", "META_DOCUMENTS"."META_DOC_ID", "META_SECTIONS"."META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_ROW" AS "META_CELL_UROW", "META_CELL_COL" AS "META_CELL_UCOL", "CEM_ID"
     FROM "LOAD"."LOADER_CELLS_STEP_1"
     INNER JOIN "LOAD"."META_DOCUMENTS" ON ("META_DOCUMENTS"."META_DOC_CODE"="LOADER_CELLS_STEP_1"."META_DOC_CODE")
     INNER JOIN "LOAD"."META_SECTIONS" ON ("META_SECTIONS"."META_DOC_ID"="META_DOCUMENTS"."META_DOC_ID") AND ("META_SECTIONS"."META_SECT_NAME"="LOADER_CELLS_STEP_1"."META_SECT_NAME")
     INNER JOIN "LOAD"."META_SETS" ON ("META_SETS"."META_SECT_ID"="META_SECTIONS"."META_SECT_ID") AND ("META_SETS"."META_DOC_ID"="META_DOCUMENTS"."META_DOC_ID") AND ("META_SETS"."META_SET_NAME"="LOADER_CELLS_STEP_1"."META_SET_NAME")
     INNER JOIN "LOAD"."USER_DEF_ATTRIBUTES" ON ("USER_DEF_ATTRIBUTES"."UDA_NAME"="LOADER_CELLS_STEP_1"."UDA_NAME")
     WHERE ("META_CELL_WUOM"=1)
     AND ("UDA_TYPE"='MATCH')
   ) "SELECTION"
  WHERE NOT EXISTS ( 
     SELECT *
     FROM "LOAD"."META_CELLS_UOM"
     WHERE ("SELECTION"."META_SET_ID"="META_CELLS_UOM"."META_SET_ID")
     AND ("SELECTION"."UDA_ID"="META_CELLS_UOM"."UDA_ID")
  )

The destination table META_CELLS_UOM is empty, the source table LOADER_CELLS_STEP_1 has about 80.000 records from which I select about 3000.

EDIT 2:

There are no cuncurrent queries. When the program hangs on the execution of above query this is a screenshot from SQL Server Mngmt Studio's Activity monitor:

Answer 1

Without looking into the exceution plan i think that the reason that the query sometimes takes longer is that the SQL server tries to cut some cornors using the statistics from the last query, but it backfires

I would say that it should be solved if you

1. Select the 3000 rows LOADER_CELLS_STEP_1 in a subquery first and join in the other data, or else the the server might consider some other table to be better, and then join that will all 80000 rows, and then filter it.
2. Skip the "not exists" clause and write it another way, like left outer join with the META_CELLS_UOM and only include the rows not matching. And also here do it as separate sub query to avoid bad optimization.

Try this (I dont know if I understand the relations right) and i might have missed a few tings, but I hope you understand.

INSERT INTO "LOAD"."META_CELLS_UOM" WITH (ROWLOCK) 
("UDA_DOMAIN_ID", "META_DOC_ID", "META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_UROW", "META_CELL_UCOL", "CEM_ID") 


SELECT "UDA_DOMAIN_ID", "META_DOC_ID", "META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_UROW", "META_CELL_UCOL", "CEM_ID"
   FROM ( 
     SELECT "META_DOCUMENTS"."UDA_DOMAIN_ID", "META_DOCUMENTS"."META_DOC_ID", "META_SECTIONS"."META_SECT_ID", "META_SET_ID", "UDA_ID", "META_CELL_ROW" AS "META_CELL_UROW", "META_CELL_COL" AS "META_CELL_UCOL", "CEM_ID"
     FROM 
     ( 
        select * from "LOAD"."LOADER_CELLS_STEP_1" where ("META_CELL_WUOM"=1) AND ("UDA_TYPE"='MATCH')
     ) LCS
     INNER JOIN "LOAD"."META_DOCUMENTS" ON ("META_DOCUMENTS"."META_DOC_CODE"=LCS."META_DOC_CODE")
     INNER JOIN "LOAD"."META_SECTIONS" ON ("META_SECTIONS"."META_DOC_ID"="META_DOCUMENTS"."META_DOC_ID") AND ("META_SECTIONS"."META_SECT_NAME"=LCS."META_SECT_NAME")
     INNER JOIN "LOAD"."META_SETS" ON ("META_SETS"."META_SECT_ID"="META_SECTIONS"."META_SECT_ID") AND ("META_SETS"."META_DOC_ID"="META_DOCUMENTS"."META_DOC_ID") AND ("META_SETS"."META_SET_NAME"=LCS."META_SET_NAME")

) "SELECTION"
   left outer join "LOAD"."META_CELLS_UOM" on ("SELECTION"."META_SET_ID"="META_CELLS_UOM"."META_SET_ID")  AND ("SELECTION"."UDA_ID"="META_CELLS_UOM"."UDA_ID")
   where "META_CELLS_UOM"."META_SET_ID" is null 
)