SQL Script in VM taking long time for execution

3
votes

I have the Execute SQL Script package that contains the script to insert about 150K records.

Problem in here is when I execute the package in the Virtual machine its taking 25 min's approx and the same package in physical machine its taking 2 min's

Question 1? Why its taking that much time to load the same data in VM. Question 2? How to solve this performance issue.

Physical machine configuration has 4GB Ram and 250GB HD + Windows server 2008 R2 + SQL server 2008 R2 Standard Edition. Virtual machine has the same Configuration

Update: The Problem is with the SQL Server in VM.

Question 1? Why its taking that much time to Run the same script in VM.

Question 2? How to solve this performance issue.

Both the batabases schema in Physical Machine and VM are identical. Other databases are also same. There was no indexing applied for that tables in both machines. Datatypes are same. harddisk as I said has the same configuration.

No RAID is done on both the machines.

Physical machine has the 2.67GHz RAM Quad Core and in the virtual machine has the 2.00GHz RAM Quad Core

Version of SQL PM:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Version of SQL PM:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

I executed the script Execution plan for both are the same as there is no difference in plan.

Vendor is HP ML350 Machine.

There are almost 20 VM's on the same physical server out of which 7 servers are active.

3
sqlsql-serverperformancesql-server-2008ssis
Is that the only thing the package does-a single execute sql task? Are the database structures identical? Run a schema compare between the two. How do data volumes compare between the two. What does the host look like? What does the disk io subsystem look like on both systems? Size matters not, iops is king – billinkc
Also, check your statistics, fragmentation, etc on the slow system. Perhaps it's heavily fragmented. The quick test as to whether it's SSIS performing slowly on the VM or it's the VM's access to disk, assuming the package is only performing the Execute SQL Task, is to run that task in SSMS/sqlcmd/SQL Agent/.NET and see if times are comparable. – billinkc
I Executed the same SQL Script in the SQL Server its taking the same time. Now the problem is with the SQL Script is taking time in the VM. What could be the reason. – M.C.Rohith
Like billinkc said, are databases on physical macchine an VM identical? Do they have the same structure, same datatypes and same indexes? Are the harddisk in both machines identical? Does one or the other have RAID or SSD? – Martin
How many other VMs are running on the physical server? Its possible that there are too many VMs fighting for cpu cycles or network bandwidth. – eabraham

3 Answers

1
votes

There's an article about properly setting SQL's configuration for a VM implementation here: Best Practices for SQL Server. Below is an excerpt, though the article includes other tips and a good performance testing plan:

Storage configuration problems are the number one cause of SQL performance issues. Usually these problems arise because the DBA requests a virtual disk of the VI admin, the VI admin places the VMDK on a LUN that may or may not meet the DBA's performance needs. For instance:

  1. VMs' VMDK files placed on VMFS volumes without enough spindles.
  2. Many VMDK files placed on a single VMFS volume which could use more spindles.
  3. Database and log files placed on the same LUN which, you guessed it, could use more spindles.

This may be obvious to some, but this problem occurs again and again. The VI administrator should be aware of a few technical items that can help understand and avoid this problem:

  1. Based on the IO demands of the DB files, a certain number of spindles should be guaranteed to this file. This means that its VMDK must be placed on a VMFS volume to accout for the SQL Server's demands and all of the other demands on that volume.
  2. Mixing sequential activity (such as log file update) and random activity (such as database access) results in random behavior. This means that the LUN configuration in the pre-virtual physical environment may not be sufficient for the consolidated environment. This is discussed some in Storage Performance: VMFS and Protocols.
  3. When storage isn't meeting the SQL Server's demands, the device latency or kernel latency (queueing time) will increase. Read up on these counters in Storage Performance Analysis and Monitoring.
0
votes

The most common cause for this problem is the lack of RAM. Having everything setup on a small 4GB RAM machine is your problem.

When you try to load those 150k rows into memory (remember, everything that happens in SSIS is in memory), a lot of those rows are being handled by your pagefile.

Pagefile on your VM is a lot slower than the one on your physical machine.

To solve this, increase the amount of RAM on your virtual machine.

0
votes

I have a similar problem.

Two client machines (one physical, one virtual) execute a batch using SQLCMD. This batch calls a Stored procedure on a physical server (so it's not a memory problem since the elaboration is only on server side).

The batch executed from the physical machine takes 20 minutes. The batch executed from the virtual machine takes 1 hour and 20 minutes.

Using SQL profiler I noted that in the case of slow execution there is a wait type ASYNC_NETWORK_IO.

Probably the virtualized network layer is not optimized.

Could you run a SQL profiler and check if you see the wait type ASYNC_NETWORK_IO?