June 1, 2009 at 7:40 am
Hi,
I currently have a +50GB database with only 1 filegroup (Primary). I am planing to archive some tables base on dates (I can hear you.. WOWOWW a great idea, I know I know).
My question is I have a RAID10 ( attach to 1 drive let say "D" (yeah we don't spend a lot on IT hardware). If I have only 1 drive, will it be better to create a different filegroup to host my archives tables even if it's on the same drive?
Thanks
June 1, 2009 at 8:36 am
I would like to say, it would not help. As far as I know, only putting objects (or files) in different physical drives or raid would help.
June 1, 2009 at 9:54 am
If you only have one disk, I'd say no. Unless you are targeting filegroup backups for recovery purposes, which for 50GB isn't probably likely, or you are trying to separate your data to reduce logical fragmentation inside your datafiles, you don't get any other benefit from splitting them apart as small as your database is.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 1, 2009 at 9:42 pm
Hi,
Its good idea to have multiple FILEGROUPS so that you can spread your data to different files which will give performance improvement as well if placed on different drives all together.
In your scenario you have 1 drive so being we have 1 file group or multiple file groups it will not make a difference as underlysing its only single DISK and SPINDLE so I am not very much optimist about getting some major performance boom untill and unless we place it on different disk all together.
If you want to check you can run PERFMON and select below counters in order to check the current status of your disk subsystem.
Performance Object : PHYSICAL DISK
Counters : Avg. Disk Queue Length
Avg. Disk sec/Read
Avg. Disk sec/Transfer
Avg. Disk sec/Write
Values of Avg. Disk sec/Read & Write should be like below.
10ms - very good
10-20ms - normal
20-50ms is slow
> 50 ms is problem...
Please refer below TECHNET article as well for IO basics.
http://technet.microsoft.com/en-us/library/cc917726.aspx
Regards
GURSETHI
June 3, 2009 at 2:37 am
If you divide your database into multiple filegroups this will force SQL Server to use multiple I/O streams to access your data.
SQL can use multiple streams on a single table in a single file if it wants to, but using multiple filegroups will force SQL to use this functionality.
If your disk subsystem cannot cope with the increased I/O load you will get queueing and your overall response time can easily get worse. Before splitting your database in this way, you should do some modelling of the likely I/O load. BOL has detals of how you can do this.
If you have a single disk system then almost certainly you will get best performance by having all data in a single file in a single filegroup.
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
June 3, 2009 at 11:19 am
We currently have one drive (D). This drive is build on a RAID10 with 7 or 8 spindles. SO I think IO might be fine. I will dig into this.
What is BOL? Sorry for this dummy question.
June 3, 2009 at 11:52 am
Since you can't control where the files go, physically, on the actual disks, there's no gain to be had from multiple filegroups. Doesn't matter whether it's actually one disk, or the 7 or 8 you have.
IO probably won't be a problem for you, with your set up, but I'd seriously recommend looking into splitting that single RAID 10 into 2 RAID 10s, one for data, one for logs. That will increase safety and also probably get you a performance boost.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2009 at 5:46 am
BOL is Books Online.
GSquared has a good idea about spiltting your RAID group.
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
June 4, 2009 at 6:01 am
EdVassie (6/4/2009)
BOL is Books Online.GSquared has a good idea about spiltting your RAID group.
I'd test that with SQLIO before implementing it in production use. A 8 disk RAID 10 is only 4 striped pairs, To split it would mean creating 2x2striped pairs which is going to affect your overall IOPS for the two disk arrays. Unless you can run this through independent channels of the RAID controller, I wouldn't expect that it would be beneficial, but would rather hurt your overall performance.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 5, 2009 at 7:20 am
Jonathan Kehayias (6/4/2009)
EdVassie (6/4/2009)
BOL is Books Online.GSquared has a good idea about spiltting your RAID group.
I'd test that with SQLIO before implementing it in production use. A 8 disk RAID 10 is only 4 striped pairs, To split it would mean creating 2x2striped pairs which is going to affect your overall IOPS for the two disk arrays. Unless you can run this through independent channels of the RAID controller, I wouldn't expect that it would be beneficial, but would rather hurt your overall performance.
As always, test, test, and test again.
I was more thinking in terms of 1x2 for logs and 3x2 for data, but you're right that the RAID controller might not give you any improvement in IO and might even make it worse.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2011 at 9:21 am
Does the host raid controller and firmware together affect the IOPS, if two RAID 10's were using the same channel ?
If each RAID-10 was setup using different channels, I understand would be ideal, but would does the controller distribute the IO proportionately, that is without any priority on one channel vs the other ? Curious. Stress testing would demonstrate these differences for any given server box.
June 2, 2011 at 11:03 am
What I heard is you have a drive called "D". which makes me assume that you are probably a standard IT shop and you have your OS installed to a drive called "C". If this is the case and you really want to help the performance of this server, move your tempdb and log files to your "C" drive. The other parts of your IT shop will have a cow when you do this right up to the point where the users start telling them how much faster the database is and how good of job you are doing. It is risky as you get server guys that keep restarting the server because the C drive fills up but it may be worth the risk. If you take this approach make sure you define a max size for your tempdb and log files.
Next time tell your boss you will not install SQL to something so yesterday. lol not like it will matter but it should make you feel better.
August 14, 2012 at 6:18 pm
Most server comes with 8 SAS drive connectors as default. You can have 4 RAID1 or 2 RAID10 without extra cost. You run out of disk groups soon because you need five distinct RAID arrays, sys db, temp db, data, log, backup. Got money to talk about extra file groups for each db?
Correct, HBA can be your bottle-neck depending how many disk RAID grouping you use.
Jason
http://dbace.us
😛
August 15, 2012 at 12:41 pm
Thank you. I appreciate the input.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply