September 10, 2008 at 2:59 am
I was having a discussion with a collegue of mine about how we can make our (large) tables faster.
My opinion: Currently we have 8 harddrives in RAID 5, i suggested to split this into 2x 4 harddrives in RAID5. Now SQL sees 2 harddrives so we can put 1 filegroup at both harddrives. The large tables can now be partitioned and these partitions can be spread out over the 2 harddrives. So when i query that table SQL will retrieve data from the 2 tables at the same time.
His opinion: just leave the 8 harddrives in 1 RAID 5. RAID 5 with 8 harddrives is 2x faster than RAID 5 with 4 harddrives. So when it would be as i suggested SQL would use (e.g.) 1 second to retrieve the data from both harddrives, but because this is done simultanious the total query will cost also 1 second. In the current situation the harddrive is 2x faster, so the query over the complete table will also take 1 second.
What do you think, is there an advantage in using seperate filegroups at diverent drives, or is my collegue right??
September 10, 2008 at 5:40 am
I don't think RAID 5 with 8 hard drives is 2x faster than RAID5 with 4 hard drives.
Regardless, RAID5 is slow and the first step to speeding things up would be to change to a faster RAID configuration. After that, spreading files and tables to individual physical drives is ideal and the direction you would probably want to go if IO is really your bottleneck. That being said, take the time to carefully consider what operations are causing IO conflicts and be sure to separate those operations as best you can.
September 10, 2008 at 6:11 am
I'm not a RAID expert, but having seperate file groups on different drives will be faster than all the filegroups on one drive, yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 17, 2008 at 12:54 pm
Generally it's unlikely to make much difference, splitting to two raids would probably degrade performance I'd guess. I'd migrate the database to multiple files ( not filegroups ) to increase the read write threads.
I'll say ( again ) what i always say how are you mionitoring to know you have a problem? what are you io completion times like for instance and are you saturating the raid with io requests?
If your write to read ratio is greater than about 20 to 80 then you need to get to raid 10 as raid 5 will be slowing you down.
However, depends on your ratio of physical to logical io too - putting your data in cache totally eliminates raid problems - latency can be due to poor log drive performance , you do have your t-log on a seperate drive ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 3:10 am
Colin's question about how you are monitoring things deserves respect. Unless you know what is causing the problem, you are unlikely to get the right solution.
The discussion you report between you and your colleage makes me think neither of you know what is causing the problem. Is it out of date statististics causing SQL to use a poor access path. Is it intermittent hardware errors on a disk causing read retries. Is it your disk controller is saturated and cannot handle more IOs regardless of how your disks are configured. Is it you have not set SQL maximum memory and Windows is paging itself to a standstill.
My advice is to research what is causing the problem before changing anything. If you change the wrong thing and the problem remains, you will loose a lot of credibility with your management and find it harder to get approval for further changes.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 18, 2008 at 3:17 am
Thanks for your answers.
Unfortunately (and as you excpected) i have no proof of anything since there is hardly any monitoring done at our servers. I will see if i can arange some kind of monitoring so i will know a little bit more about what's going on.
September 18, 2008 at 12:33 pm
there's some stuff which may help here http://www.grumpyolddba.co.uk/monitoring/monitoring.htm
and I have quite a lot of stuff on my blog but it takes a while to find - blog link on my web site.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply