We've come across a perplexing problem with our database application. We're running the application in 3 different (but similarly spec'd) environments.
Select Insert Env1 0.81ms 0.94ms Env2 0.69ms 5.79ms Env3 0.52ms 15.61ms
We benchmarked disk and memory performance in the 3 environments. Env3 is slightly faster than Env2 which is slightly faster than Env1 (by no more than 10%) As you might expect, Selects in Env3 are slightly faster than in Env2 which are slightly faster than in Env1. However, Inserts in Env1 are 5 times quicker than in Env2 and 15 times quicker than in Env3. What could possibly account for the very poor performance of writes in Env2 and Env3?
Some background information in case it's relevant.
The figures above were gathered by a test application we wrote to try and diagnose the problem. The Selects & Inserts are basic and performed upon a single small table.
Our test application...
a) was written in C# and uses the System.Data.SqlClient.SqlConnection based classes.
b) calculated the average times by carrying out the action 1000 times and dividing the time taken by 1000.
c) worked in 2 modes...
i. Our code called the insert/select/update 1000 times
ii. Our code called a single stored procedure which called the insert/select/update 1000 times
d) runs on the same machine as the database
Updates suffered similarly to Inserts except when the stored procedure called the update. When the stored procedure called the update in Env3 it was 4 times slower than Env1, but when the client called the 1000 updates it was 17 times slower. The below expanded table illustrates the difference. environments.
Select SP Select Insert SP Insert Update SP Update Env1 0.81ms 0.015ms 0.94ms 0.72ms 0.83ms 0.04ms Env2 0.69ms 0.019ms 5.79ms 5.32ms 7.42ms 0.09ms Env3 0.52ms 0.020ms 15.61ms 14.37ms 15.27ms 0.15ms
We set up 'Environment 1'. Our client set up 'Environment 2 & 3'. Environment 2 is a VMWare image. When we first noticed how slow inserts and updates were, we brought a copy of the image to our hosting centre. The performance problems disappeared.
Each environment is running SQL Server 2005 standard edition (64bit) on Windows Server 2008 (64 bit)
Major differences between the environments are
Env1: Running in Hypervisor on a Dell T610 with 2 quad core Intel Xeon E5520 Processors. (The VM has 3 dedicated cores and 6Gig of dedicated RAM.)
Env2: Running on VMWare VSphere on a HP Proliant DL585 G2 with 4 Dual Core 2.8Ghz AMD Opteron 8220 processors. (The VM has 3 dedicated cores and 6Gig of dedicated RAM.)
Env3: Running on physical box - HP Proliant DL380 G5, 2 * Dual Core Intel Xeon 3Ghz processors, 6GB RAM.
Env1 uses RAID0, Env2 & 3 use RAID5
When we copied the Env2 image to our server, we ran it using VMPlayer. We could not replicate the performance problems.
Env1 runs on our network. Env2 & 3 run on our clients network.
We tried Shared Memory, Named Pipes and TCP as the communication mechanism with no appreciable difference.
We’ve tried rebuilding indexes, dropping and recreating tables etc. The poor performance happens for all inserts/update on all tables.
We have 2 questions...
1) What could possibly account for the very poor performance of writes in Env2 and Env3? 2) What tool can we used to see where all those extra milliseconds are being used up?
Thanks for any help you can give us on this one.