SQL Clone
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
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
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59832 Visits: 17947
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 Modens 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)
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
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59832 Visits: 17947
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 Modens 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)
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11822 Visits: 7444
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" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214280 Visits: 46266
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, MVP, M.Sc (Comp Sci)
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


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
We updated the statistics, but that did not help. We have separate RAID-10 arrays for the OS, DBs, & Trans Logs.
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
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
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11822 Visits: 7444
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" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214280 Visits: 46266
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, MVP, M.Sc (Comp Sci)
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


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