Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DerRubberDucky
Starting Member
1 Post |
 Posted - 2011-01-26 : 17:44:11
|
| 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 InsertEnv1 0.81ms 0.94msEnv2 0.69ms 5.79msEnv3 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. 1. 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. 2. 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 3. 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. Select SP Select Insert SP Insert Update SP UpdateEnv1 0.81ms 0.015ms 0.94ms 0.72ms 0.83ms 0.04msEnv2 0.69ms 0.019ms 5.79ms 5.32ms 7.42ms 0.09msEnv3 0.52ms 0.020ms 15.61ms 14.37ms 15.27ms 0.15ms 4. 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.5. Each environment is running SQL Server 2005 standard edition (64bit) on Windows Server 2008 (64 bit) 6. Major differences between the environments areEnv1: 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 uses RAID5When 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. 7. We tried Shared Memory, Named Pipes and TCP as the communication mechanism with no appreciable difference.8. 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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Selects good, Inserts bad