Parallel Inserts using Table Lock

• tung858

  SSC Veteran

  Points: 241

  More actions

  August 29, 2020 at 1:46 am

  #3784558

  So I had a performance issue with a developer attempting to import over a billion records into a hash table (no indexes no keys etc...) using similar code "INSERT  INTO TABLENAME SELECT COLUMN A, COLUMN B FROM QUERY"

  It took about 5.5 hours total, but with the below hack I was able to drop it down to 3.5 hours.

  By including the "INSERT  INTO TABLENAME WITH (TABLOCK) SELECT COLUMN A, COLUMN B FROM QUERY" it appears starting with SQL Server 2016+, inserts will be using parallelism compared to the old serial method of inserting records.   The maxdop I had for the server is 8.

  Tung Dang
  Azure and SQL Server DBA Contractor / Consultant
  DataZip

• Phil Parkin

  SSC Guru

  Points: 247119

  More actions

  August 29, 2020 at 6:48 am

  #3784588

  What is your question?

  ---

• Eirikur Eiriksson

  SSC Guru

  Points: 182943

  More actions

  August 29, 2020 at 1:33 pm

  #3784607

  Phil Parkin wrote:

  What is your question?

  +1000

  

  Phil has a very good question!