We are using SQL Server 2012.
The same database myDB (Simple Recovery mode, Autogrowth by 1000 MB on the data, and by 100 MB on the Log) exists in 2 different servers, server A and B.

Server A configuration
myDB database (7.8 Gig) on drive E (381 Gig free space), and transaction log (1.8 Gig) on drive F (117 Gig free space).
4 processors @ 2.4 GHz, 32 Gig RAM.

Server B configuration
It's a Virtual machine.
Both myDB database (7.8 Gig, same with the one on Server A) and transaction log (205 Meg) are on drive E (12 Gig free space).
2 processors @ 2.67 GHz, 4 Gig RAM.

Even though Server A is a much more powerful machine than Server B, data is being written to Server B faster than on Server A.
The application that writes to theses database are located on a different server, and the application server that write to SQL Server A is also a much more powerfull machine than the application server that write to SQL Server B.

Why would the SQL Server on a more powerful machine writes slower than the one on a less powerful machine ?

Thank you.

• Merged by Shanky_621 Thursday, March 06, 2014 10:42 PM duplicate post

We are using SQL Server 2012.
The same database myDB (Simple Recovery mode, Autogrowth by 1000 MB on the data, and by 100 MB on the Log) exists in 2 different servers, server A and B.

Server A configuration
myDB database (7.8 Gig) on drive E (381 Gig free space), and transaction log (1.8 Gig) on drive F (117 Gig free space).
4 processors @ 2.4 GHz, 32 Gig RAM.

Server B configuration
It's a Virtual machine.
Both myDB database (7.8 Gig, same with the one on Server A) and transaction log (205 Meg) are on drive E (12 Gig free space).
2 processors @ 2.67 GHz, 4 Gig RAM.

Even though Server A is a much more powerful machine than Server B, data is being written to Server B faster than on Server A.
The application that writes to theses database are located on a different server, and the application server that write to SQL Server A is also a much more powerfull machine than the application server that write to SQL Server B.

Why would the SQL Server on a more powerful machine writes slower than the one on a less powerful machine ?

Thank you.

• Merged by Shanky_621 Thursday, March 06, 2014 10:42 PM duplicate post

We are using SQL Server 2012.
The same database myDB (Simple Recovery mode, Autogrowth by 1000 MB on the data, and by 100 MB on the Log) exists in 2 different servers, server A and B.

Server A configuration
myDB database (7.8 Gig) on drive E (381 Gig free space), and transaction log (1.8 Gig) on drive F (117 Gig free space).
4 processors @ 2.4 GHz, 32 Gig RAM.

Server B configuration
It's a Virtual machine.
Both myDB database (7.8 Gig, same with the one on Server A) and transaction log (205 Meg) are on drive E (12 Gig free space).
2 processors @ 2.67 GHz, 4 Gig RAM.

Even though Server A is a much more powerful machine than Server B, data is being written to Server B faster than on Server A.
The application that writes to theses database are located on a different server, and the application server that write to SQL Server A is also a much more powerfull machine than the application server that write to SQL Server B.

Why would the SQL Server on a more powerful machine writes slower than the one on a less powerful machine ?

Thank you.

Hello,

Please dont post duplicate threads ,Its not a good practice in forum to do.Please wait for somebody to respond.How you compared the writing speed ,are you sure both hardware on machine are equivalent.Whats is query plan in both the systems for same query you run.Can you post all data

What do you mean by write speed (how do you measure it)?

Is it number of IOPS, random saves, SSIS transfer or something else?

Anyway, most probably your operations are bound to speed of the SAN or HDDs.

In such case your storage in the virtual environment is much faster than in physical.

Sorry for posting twice, because I didn't know what forum is the best one to post this question to.

No, both hardware is different, the database is the one that's the same in both machine.

Server A (more powerful machine, non VM) is Intel XEON CPU E5-2609 @2.4 Ghz 4 cores, 32 gig RAM
Server B (less powerful machine, On VM) is Intel XEON CPU X5650 @2.67 Ghz 2 cores, 4 gig RAM

The data written has a time stamp.
On a busy time (when there is a lot of data coming in), say at 10:33:00 am, Server A is writing data from 10:32:00 am.
While Server B already writes data from 10:33:00 am. So, in this case Server A is 1 minute behind.

Data keeps streaming to the program, and as it comes in, it writes the data using a stored procedure that has the query "INSERT INTO".

Server A is only behind during busy time, ie when there is a lot more data than usual (say 10,000 more records per/minute) that needs to be written to the database.
For ex: during non busy time, the most it writes about 30,000 records in a minute, but during busy times it writes about 40,000 in a minute.

The execution plan shows that it spends 100% on Clustered Index Insert. Again, both servers have the exact same database, just different hardware on both servers.

 Thank you.

 

Simple inserts are limited by IO.

Use perfmon on boht servers to track number of IO operation per second, latency and throughput.

On VM you may have expensive, fast SAN and it could explain the difference,

Although the number of cores, their speed and the available memory can be relevant for determining a query's result, when it comes to writes, the bandwidth and I/O speed is usually the most important factor.

If the drive that hosts the database and/or log file is shared on one machine, and not shared on the other machine, then you might see what you are seeing.

If the drive is a logical drive that uses multiple dedicated disks, then this will typically outperform a single disk drive.

If the drive uses a Solid State Disk, then this typically outperforms a Hard Disk.

If the logical drive uses a RAID0, RAID1 or RAID10 configuration, then this typically outperforms a RAID5 configuration for writes.

In other words: the machine that performs better probably achieves higher I/O rates, because it has a "better" drive.

Thank you for your replies.

I will forward this to our server people.

Thanks again.

I found on Server A (the machine that writes slow during busy time), the Enable write caching on the device is DISabled. And, it is Enabled on Server B (the machine that can keep up even during busy time).

Could this be the difference in writing speed ?

Yes, in the worst case situation, it can have considerable impact. It depends on the type (and rate) of transactions.

Thank you.

Is there a SQL Caching that I need to also look at in this situation ?
Is it possible that on the machine that keeps up there is a SQL Caching, but not on the machine that can't keep up ?
What do I need to check for this ?
Thank you very much.

Make sure that the write caching is properly battery backed up if you want to sustain an unexpected down without a corrupt database! You can use SQLIOSIM for this and read this for more info: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx