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:
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!