Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL INSERTS are significanlty slower on newer server


SQL INSERTS are significanlty slower on newer server

Author
Message
joshdbguy
joshdbguy
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 725
Can you also check and see the location of your TempDB? What drive is it on, what's the configuration of that drive?
mberran
mberran
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
Just a quick update - we were able to resolve the poor performance issue and I wanted to share the findings.

As I mentioned, this was new hardware. It turns out that the read/write ratio on our storage controller cache was set to the default of 100%read and 0% write. Our old server was set to 25% read/75% write. When we updated the new server to match the setting of the old server, we saw significant improvement in our particular test involving DB write. However, the new server was still not performing as well as the old server (using our test program, it was only 2x slower instead of 8x slower now).

It also turns out that the power profile in the BIOS was set to Balanced. This setting was changed to High Performance and we received another bump in performance.

Estimated time in seconds per 1000 inserts:
Old Server: 0.53
New Server (original/default configuration): 4.33
New Server (after changing read/write storage cache ratio): 0.96
New Server (after changing power profile): 0.44

A number of other things were tried after the read/write ratio was changed and none seemed to have significant impact on our particular test. Included in this were changing the number of spindles per RAID, changing read/write ratio from 75% write to 100% write, testing against a SOLID state drive (to rule disk I/O issues), various configurations of DB and transaction logs on same and different drives, and changing the hyper threading setting.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search