Extrem slow INSERT without reason

Thomas Franz 2017-08-04 11:57:29

The first query (History panel) in the attached SQL Sentry Plan Exlorer file contains a unpivoted SELECT on sync.t_sid_abruf with a LEFT JOIN to the target table (pld.t_leistungswerte). Since the target is empty, it would return all ~78k rows from the source (~98k after the pivot). It will return the results in ~0.2 seconds.

The second query is exact the same, except that I added this time an INSERT into the target table (which has two foreign keys to small lookup tables (< 10 rows each)). When I execute the second query it takes ~2 minutes just to insert the 98k rows and I have absolut no idea, why. Yes, it is (whyever) using two MERGE JOIN to verify the two FKs and it needs two expensive SORTs, which are spilling to TEMPDB, because the optimizer seems to think that the semi right join to the empty target could reduce the number of rows, but we are talking about < 100k rows (2,5 MB granted memory) and the query runs on my local SSD (SQL Server 2014 Dev) without any other SQL load.... Recompiling, statistic updates, placing the UNPIVOTed dataset in a #TempTable does not help...

Thomas Franz 2017-08-04 12:22:30
I found the culprint by myself.

The target table had an ID value with DEFAULT (NEXT VALUE FOR pld.s_leistungswertid) ). The sequence was created with CACHE = 20 -> ~5000 updates to the system table which manages the sequences.

After changing the Sequence to CACHE = 200 the INSERT takes only ~11 seconds and with CACHE = 20000 only 1,5 seconds.