September 3, 2008 at 8:42 am
I have a database that is 593GB. It is all in the PRIMARY file group. I think I read somewhere that I could gain performance by using multiple file groups. Is this true and if it is how can I go about doing something like this?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 3, 2008 at 9:00 am
For moving objects between filegroups you can check following article: http://www.mssqltips.com/tip.asp?tip=1112.
Large database could benefit from splitting into filegroups between number of physical devices. Optimizing IO with file groups you can take into consideration usage patterns (Read, Read/Write, LOB). If you are on SAN, splitting into filegroups will not always bring you performance benefits.
September 3, 2008 at 9:13 am
That was news. We are currently on a SAN. So I guess it won't give us anything.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 3, 2008 at 1:20 pm
Hi Art,
I would recommend to check James Luetkehoelter post on 'Filegroups Part II: Separating objects' and see other benefits than performance that you could gain from creating few file groups (http://sqlblog.com/blogs/james_luetkehoelter/archive/2008/02/12/filegroups-part-ii-separating-objects.aspx), even though not all might apply to your specific situation.
Also have you checked with your SAN vendor, if they don't provide recommendations on how to configure their product for SQL Server?
Linchi Shea blog (http://sqlblog.com/blogs/linchi_shea/archive/tags/SAN/default.aspx) is a good repository on SAN performance testing/optimization.
September 3, 2008 at 1:48 pm
With sql7 and sql2000 you're better off putting your user objects NOT in the primary filegroup, but is your own added filegroup(s).
Reasons ? Think IO parallisme.
- you can place an object in a predefined filegroup
- you can have your own filegroup assigned to be the default (in stead of primary).
- you can place the different files on different disks, ...
With SQL2005 it even has more advantages, because of the partial restore they support ! (If you can restore the catalog (i.e. primary), then you can restore a single filegroup and have it available for use)
My advise: Don't put any of your persistent objects (tables, indexes) in the primary filegroup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply