We have a query that we run to import a Master Do Not Call List into our database with about 200 million records.

We first mark the existing numbers in the database table with an expiration date. This process takes about 19hrs or more, and eats up the log files, and tempdb log files.

The second part of the query is the import from a text file located on the c: drive. This part also takes quite sometime, and last time SQL Studio crashed so the query tanked and did not finish.

Below is the query that we run:

enter image description here

To give some background on the server setup: Windows 2016 standard intel xeon 8cores 2.59ghz 128gb ram sql 2016 standard

Data volume located on 3par 8200 SAN spinning drives.

Log volume located on 3par 8200 SAN spinning drives (current size is now 1.46tb because 3 times we ran out of space)

Temdb data and log volumes located on 3par SAN as well. (one volume)

Is there a better way to run this query and make it more efficient? Should we create a job with two steps one for the update expiration, and second for bluk insert? Should we move data and log volumes to SSDs on the 3par...currently they are located on spinning drives.

What do you think we can do to make the process more efficient?

Any information would be greatly appreciated! Thanks!

  • 3
    How did you get to batchsize = 1000? Why are you doing this from a client GUI? Have you considered using TABLOCK? Have you read The Data Loading Performance Guide? – Aaron Bertrand May 31 '19 at 16:54
  • While the processes is running examine sys.dm_exec_session_wait_stats for the session performing the load. See docs.microsoft.com/en-us/sql/relational-databases/… And do you really need to update every row in the table before the load? – David Browne - Microsoft May 31 '19 at 19:25
  • These are all new records? Where does it come from? And is there any way to load a staging table that then compares just the identifying rows to see if new numbers are added? How did you store the existing numbers and from what source did you get the new ones? Is there a way to consolidate so that only new or updated information on existing numbers is added? Just some preliminary questions. Log space consumption is really a sign of a lot of in-memory consumption. 200million rows of NVARCHAR(20) wouldn’t consume this much, so there is definitely more to the story. – clifton_h Jun 1 '19 at 19:30
  • Thanks for your replies. I am not exactly sure if the records are all new....comes from a text file our team created after compiling the list. You mention Log space is a sign of memory consumption. I do notice that SQL uses about 96% of the total memory of system. I have read in some places that this is normal for SQL. Do you recommend we throttle this somehow? – evan631 Jun 4 '19 at 23:41
  • Do you need the memory for something else? You need a reservation for the operating system, but no, you shouldn't limit it otherwise IMHO. SQL Server works best when as much of the data as possible is in memory. Throttling it just ensures that SQL Server will run slower. – Aaron Bertrand Jun 5 '19 at 11:38

The first problem I see is that you are performing a massive update (19+hours in duration) in a single run, and that is why your server is running low in resources. You need to perform the updates in batch. They will be faster, and your server setup should be able to cope with the resource load.

Try the following:

        @BatchSize INT; 

SET @BatchSize = 2000;

SET @Rows = @BatchSize; 

WHILE (@Rows = @BatchSize)
      UPDATE TOP (@BatchSize) tab
      SET    tab.expiration = dateadd(day, 5, getdate())
      FROM  I3dialer.dbo.MasterDNC tab
      WHERE tab.expiration < dateadd(day, 5, getdate());
      SET @Rows = @@ROWCOUNT;

Before testing this, be sure that there's an index in the expiration field to accelerate the query.

For the second part of your problem, other than increasing the batch insert to say, 10000 should help a bit. You can actually experiment with this to find your optimal batch size, for example, testing against 1 million records to see if there's any performance difference. There are also other things to consider. You should first load the data into an empty table, perform whatever operations you need to clean up your data, and finally insert them into your main table.

In summary, you should now have a 3 part process:

  1. Perform the batch update on your main table
  2. Insert data into a temporal or stage empty table
  3. Insert data from your stage table to your main table

You should definitively get a better performance after implementing this. Do share your results.

| improve this answer | |
  • Thanks so much for these options. We will discuss in the AM. – evan631 Jun 4 '19 at 23:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.