I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using the Bulk Insert command to insert data from text files to tables in the database.
For the two tables importing data, there are no indexes/triggers/constraints, just two empty tables to load data into.
For the two text files to load, each of them has around 20M rows.
Bulk insert table1 FROM 'table1.txt' WITH (FIELDTERMINATOR ='|', ROWTERMINATOR = '0x0a', TABLOCK)
Bulk insert table2 FROM 'table2.txt' WITH (FIELDTERMINATOR ='|', ROWTERMINATOR = '0x0a', TABLOCK)
I want to compare the running time under different scenarios.
Scenario 1: two queries running serially in one editor:
- Query 1: 1 m 18 s
- Query 2: 1 m 2 s
- The total running time is 2m 20s.
Scenario 2: each query in a separate editor of the same Studio instance, running concurrently:
- Query 1: 2 m 36 s
- Query 2: 2 m 09 d
- The total running time is 2m 40s
Scenario 3: each query in a separate Studio instance, running concurrently:
- Query 1: 2 m 29 s
- Query 2: 2 m 19 s
- The total running time is 2m 29s
While scenario 2 and 3 seem to run queries concurrently, why the running time hasn't change much? For all scenarios, the CPU usage is between 17% to 21%, the disk usage is 50MB to 70 MB per second. No significant differences between the usage of CPU and disk are observed.
I am wondering what's happening under the hood? How does SQL Server execute these queries? Why is the running time nearly the same for all three scenarios? Is there any way to speed it up?
I'm using SQL Server 2016 Developer Edition on Windows 10 64-bit. I have quad core i7 and SSD in my laptop.
After testing more scenarios with 4 queries running together, I think the possible cause of low usage of CPU and disk is the hyperthreading with my Core i7:
With two editors running two queries concurrently, the most CPU usage is 25%.
With 4 editors running 4 queries concurrently, the most CPU usage is 50%.
Are you guys aware of ways to let one query exclusively use one core? Turning off hyperthreading is not an option for me, since my ThinkPad T460p doesn't support such an option in the BIOS.