question

CPU shouldn't be the primary bottleneck for inserting lots of data. It's going to be your disks. In general, for large inserts, if you can, remove non-clustered indexes and then rebuild them after the load. Also, breaking the load into smaller transactions might help (avoiding log growth and other issues). Also, how are you measuring CPU? Directly within the VM is a bad place. You need to measure it from the hardware to know what's going on with CPU and memory. The other thing you can look at is the wait stats during the run to see what the query is waiting on. You can use sys.dm_os_wait_stats at the start of the process and part way through to see what has changed. You can also look at sys.dm_exec_requests as the process is running to see what it's waiting on. That will all help you identify what is causing the slow behavior.

You mentioned that this is a VM, do you have other VMs running on the same machine that might be using up resources (particularly ones that affect disk access, as Grant pointed out)? Similarly, are there other processes running in this VM that might slow it down? Grant also mentioned looking at what it is waiting on, expecially when dealing with excessively slow processes, I would certainly look for a possible blocking situation. But I would also focus on the data source? Where are the 10 million records worth of data coming from, and could that be forming the bottleneck here? This would particularly be a concern if they are coming from another machine which might have a slow (or simply busy) network connection to the SQL Server.

It's a hyper vm. What's weird is only the load(insert into) takes so long but when i do delete or other dml,ddl it works fine.