December 10, 2009 at 9:11 am
Guru has a good point. It may be fruitful at this point to examine the hardware to see if there is anything you can squeeze out of it.
As he, Gail, and Grant have said though - this much data being pumped around is going to be slower. You are getting pretty good performance for what it is doing.
Does the app really need to pull all 2.1 million records for this process?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 10, 2009 at 9:48 am
If you are looking for an easy improvement in the speed by improving the Hardware I can recommend using a SSD
We installed one to speed up a server that we could not change the horrible code, and the results have been good.
December 11, 2009 at 9:08 am
whats hardware is it running on?
Nigel Moore
======================
December 11, 2009 at 9:37 am
server is dl380 with 15k rpm drives...
raid 5 set.
December 11, 2009 at 9:51 am
Ow. RAID 5.
How many spindles? How are the data files laid out? Which log and data files are on which physical disk(s)?
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
December 11, 2009 at 9:59 am
6 disks make up the RAID 5.
data and logs are split onto separte drives; tempdb on its own drive.
December 11, 2009 at 10:08 am
jwa082276 (12/11/2009)
data and logs are split onto separte drives; tempdb on its own drive.
Separate physical drives or just partitions on the RAID array?
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
December 11, 2009 at 10:10 am
partitions on raid array...
However, in the month I will be moving my data to our san system. I will create one volume for each lun I create; so this should help improve performance. The san drives are 15k rpm drvies...on the san it will be RAID DP.
December 11, 2009 at 10:28 am
jwa082276 (12/11/2009)
partitions on raid array...However, in the month I will be moving my data to our san system. I will create one volume for each lun I create; so this should help improve performance. The san drives are 15k rpm drvies...on the san it will be RAID DP.
RAID DP is a definite step up in performance.
I think you will see some performance gains once your Disk subsystem "upgrade" is complete. It should make a difference in performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 11, 2009 at 1:47 pm
Jason, can you please explain to me how RAID DP will be a step up in performance from RAID 5? I am not too familiar with that type.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2009 at 5:06 pm
RAID DP is what NETAPP calls RAID-6 for their SAN implementations.
DP stands for double-parity.
From the NetApp docs
The performance of RAID-DP volumes is comparable to that of RAID 4. Read operation performance is exactly the same for each type of RAID group. Depending on the type of write, performance on RAID-DP can be about 2% to 3% slower than that of RAID 4. The reason for this small performance difference is that an extra write occurs to the second diagonal parity disk on RAID-DP volumes. There is no discernable impact to the CPU utilization from running RAID-DP versus RAID 4.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 11, 2009 at 10:05 pm
CirquedeSQLeil (12/11/2009)
RAID DP is what NETAPP calls RAID-6 for their SAN implementations.DP stands for double-parity.
From the NetApp docs
The performance of RAID-DP volumes is comparable to that of RAID 4. Read operation performance is exactly the same for each type of RAID group. Depending on the type of write, performance on RAID-DP can be about 2% to 3% slower than that of RAID 4. The reason for this small performance difference is that an extra write occurs to the second diagonal parity disk on RAID-DP volumes. There is no discernable impact to the CPU utilization from running RAID-DP versus RAID 4.
I didn't think that RAID 4 was faster than RAID 5, but rather that it could be slower due to bottlenecking on the single parity drive. I also take that gobbledygook as market-speak. I truly have a problem believing that computing and laying down 2 parity stripes will result in something faster than doing 1 parity stripe. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 12, 2009 at 11:56 am
TheSQLGuru (12/11/2009)
CirquedeSQLeil (12/11/2009)
RAID DP is what NETAPP calls RAID-6 for their SAN implementations.DP stands for double-parity.
From the NetApp docs
The performance of RAID-DP volumes is comparable to that of RAID 4. Read operation performance is exactly the same for each type of RAID group. Depending on the type of write, performance on RAID-DP can be about 2% to 3% slower than that of RAID 4. The reason for this small performance difference is that an extra write occurs to the second diagonal parity disk on RAID-DP volumes. There is no discernable impact to the CPU utilization from running RAID-DP versus RAID 4.
I didn't think that RAID 4 was faster than RAID 5, but rather that it could be slower due to bottlenecking on the single parity drive. I also take that gobbledygook as market-speak. I truly have a problem believing that computing and laying down 2 parity stripes will result in something faster than doing 1 parity stripe. 🙂
Understood.
We experienced significant IO performance improvement by going from RAID 5 to RAID DP in a previous life. Thus I was basing my claims of the OP seeing improvement on personal experience with the product. Of course, I would test configurations on the next go around and make sure that the config would work best for the new environment or the next client.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 12, 2009 at 1:31 pm
Curious. I wonder if that improvement came simply because NetApp was doing something 'dumb' with the RAID 5 setup you had. I have heard horror stories from multiple clients who tried NetApp for serving up sql server workloads, so I advise all my clients to steer clear of them for that purpose. Maybe they have improved things recently...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2009 at 1:24 am
NetApp DP, why it can be (allegedly) as fast as RAID 10, even though typical RAID 6 is always slower than RAID 5...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply