March 26, 2014 at 11:43 am
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.
March 26, 2014 at 11:44 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2014 at 11:52 am
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?
March 26, 2014 at 11:54 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 12:14 am
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; Theyll drag you down to their level and beat you with experience
March 27, 2014 at 2:53 am
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
March 27, 2014 at 9:36 am
We updated the statistics, but that did not help. We have separate RAID-10 arrays for the OS, DBs, & Trans Logs.
March 27, 2014 at 9:37 am
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
March 27, 2014 at 10:30 am
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; Theyll drag you down to their level and beat you with experience
March 27, 2014 at 10:59 am
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
April 2, 2014 at 11:01 am
Can you also check and see the location of your TempDB? What drive is it on, what's the configuration of that drive?
April 4, 2014 at 9:38 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply