<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Filegroup vs performance
14 posts, Page 1 of 2
1
2
»»
Filegroup vs performance
Rate Topic
Display Mode
Topic Options
Author
Message
Rem70Rem
Rem70Rem
Posted Monday, June 01, 2009 7:40 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:47 PM
Points: 201,
Visits: 649
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
Post #726652
SQL ORACLE
SQL ORACLE
Posted Monday, June 01, 2009 8:36 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473,
Visits: 1,314
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.
Post #726694
Jonathan Kehayias
Jonathan Kehayias
Posted Monday, June 01, 2009 9:54 AM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Post #726783
GURSETHI
GURSETHI
Posted Monday, June 01, 2009 9:42 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, April 26, 2013 12:57 PM
Points: 92,
Visits: 280
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
Post #727112
EdVassie
EdVassie
Posted Wednesday, June 03, 2009 2:37 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 2,621,
Visits: 2,758
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.
Author:
SQL Server FineBuild
1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005.
25 March 2013
: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #727973
Rem70Rem
Rem70Rem
Posted Wednesday, June 03, 2009 11:19 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:47 PM
Points: 201,
Visits: 649
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.
Post #728391
GSquared
GSquared
Posted Wednesday, June 03, 2009 11:52 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442,
Visits: 9,571
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
Post #728414
EdVassie
EdVassie
Posted Thursday, June 04, 2009 5:46 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 2,621,
Visits: 2,758
BOL is Books Online.
GSquared has a good idea about spiltting your RAID group.
Author:
SQL Server FineBuild
1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005.
25 March 2013
: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #728840
Jonathan Kehayias
Jonathan Kehayias
Posted Thursday, June 04, 2009 6:01 AM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Post #728851
GSquared
GSquared
Posted Friday, June 05, 2009 7:19 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442,
Visits: 9,571
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
Post #729669
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
