Cpu usage is low but sql performance is slow on vm
I have a sql server on vmware and I am doing a simple insert of 10 million records and it is taking me 2 hours to run the query. My cpu useage is very low but still it's taking long time. Any suggestions on how to solve the problem? Thanks
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.