Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL INSERTS are significanlty slower on newer server Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 11:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:54 AM
Points: 142, Visits: 290
Can you also check and see the location of your TempDB? What drive is it on, what's the configuration of that drive?
Post #1557608
Posted Friday, April 4, 2014 9:38 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 4, 2014 9:38 AM
Points: 5, Visits: 8
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.
Post #1558573
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse