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, March 26, 2014 11:43 AM
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
We currently use SQL Server 2005 that is on a Windows 2003 32 bit machine with max RAM (4 GB I think). The hardware is probably 6-8 years old.

We are working on switching over to a newer server. I'm not sure of all the specs, but the hardware is new and we it is Windows 2008 server 64 bit machine with 32 GB ram. Both old and new server used some type of RAID configuration. The network guy said the RAID on the new server should be more efficient. The new server also has SQL Server 2005 on it for now with plans to upgrade later this year.

While running a number of tests, we basically found that the READS are significantly faster and the WRITES are significantly slower. For testing purposes to research this issue, we have an .NET program sitting on a separate application server that runs 10000 inserts -- one at a time. On average for 1000 of these inserts, it take 0.53 seconds on the old server and 4.33 seconds on the new server. We ran this test a number of times and get the consistent execution time with each test. Our network guy monitored he new server during one of these test and said he is not seeing any issues jump out when looking at performance monitor statistics (RAM, page faults, disk I/O, etc).

Any ideas of what may be going on? Are there any SQL Server settings/options we can focus on?

Any help is appreciated. Thanks.
Post #1555111
Posted Wednesday, March 26, 2014 11:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
Did you update statistics after you upgraded from 2005 to 2008?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1555112
Posted Wednesday, March 26, 2014 11:52 AM
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
Sorry, I didn't realize I selected the SQL 2008 forum. We are using SQL Server 2005 on both servers. The new server pretty much has upgraded OS and hardware.

Can this be moved or should I just repost?
Post #1555114
Posted Wednesday, March 26, 2014 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
mberran (3/26/2014)
Sorry, I didn't realize I selected the SQL 2008 forum. We are using SQL Server 2005 on both servers. The new server pretty much has upgraded OS and hardware.

Can this be moved or should I just repost?


No biggie. I just misread your post (even though it is in the 2008 forum). You might want to take a look at your stats anyway.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1555116
Posted Thursday, March 27, 2014 12:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
Both old and new server used some type of RAID configuration. The network guy said the RAID on the new server should be more efficient. The new server also has SQL Server 2005 on it for now with plans to upgrade later this year
Different RAID types offer different performance benefits for reads versus writes - can you find out exactly which RAID types are specifically being used for the database files? Could very well be that your log file (LDF) is sitting on some RAID 5 (or worse) and you're seeing the performance hit due to waits relating to the log file. The same could apply to your data file(s)...

If your stats were good before, they should be good now (as no DB upgrade has occurred yet from 2005 to 2008). As others have mentioned, it wouldn't hurt to update the statistics any way, and maybe check for index fragmentation.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1555278
Posted Thursday, March 27, 2014 2:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
What wait types are you seeing on your data modification queries?
What do the disk latency counters look like (physical disk sec/read and physical disk sec/write)?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1555314
Posted Thursday, March 27, 2014 9:36 AM
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
We updated the statistics, but that did not help. We have separate RAID-10 arrays for the OS, DBs, & Trans Logs.
Post #1555508
Posted Thursday, March 27, 2014 9:37 AM
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
GilaMonster (3/27/2014)
What wait types are you seeing on your data modification queries?
What do the disk latency counters look like (physical disk sec/read and physical disk sec/write)?


I requested the information and here is what I was sent:
C: (OS) – Disk sec/Read = 0.005
C: (OS) – Disk sec/Write = 0.012
D: (DBs) – Disk sec/Read = 0.019
D: (DBs) – Disk sec/Write = 0.009
E: (TransLogs) – Disk sec/Read = 0
E: (TransLogs) – Disk sec/Write = 0.003
Post #1555510
Posted Thursday, March 27, 2014 10:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
I believe Gail is referring to SQL Server-related types, for example (PAGELATCH_EX, ASYNC_NETWORK_IO, CXPACKET, and so forth).

Okay, RAID-10. To push a little further - how many spindles are making up the array, and at which speed are the drives?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1555540
Posted Thursday, March 27, 2014 10:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
mberran (3/27/2014)
GilaMonster (3/27/2014)
What wait types are you seeing on your data modification queries?
What do the disk latency counters look like (physical disk sec/read and physical disk sec/write)?


I requested the information and here is what I was sent:
C: (OS) – Disk sec/Read = 0.005
C: (OS) – Disk sec/Write = 0.012
D: (DBs) – Disk sec/Read = 0.019
D: (DBs) – Disk sec/Write = 0.009
E: (TransLogs) – Disk sec/Read = 0
E: (TransLogs) – Disk sec/Write = 0.003


Those look OK.
And the wait types you're seeing?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1555562
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse