question

liton avatar image
liton asked

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
sql serverperformancecpuvmwarevm
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

liton avatar image
liton answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.