I agree 100% with Michael. I too work in a large financial organization and
each of our databases are split into different file groups for data
(sometimes several for data alone), clustered indexes, secondary indexes,
and logs.
I also want to highlight his most important point:
"If you're running a single local disk, filegroups don't help you much as
windows still sees one disk. You have to either have multiple disks, in the
case of a SAN, multiple LUNs, that windows sees as separate disk and
therefore manages with separate 'channels'. "
Splitting your single existing drive into multiple logical drives is not
going to help as it is still on a single disk with a single channel on a
single controller.
Trevor
-----Original Message-----
From: Rogers Michael L.
[mailto:mssqldba-ezmlmshield-x19119573.[Email address protected]
Sent: Thursday, July 28, 2005 11:10 AM
To: LazyDBA Discussion
Subject: RE: Filegroups
Filegroups have made a meaningful difference in performance in our
organization, but it's important to know where the I/O is happening.
We run a major financial package where the G/L detail table is
constantly being accessed, a factor of magnitude greater than the other
tables. By separating out this one table in a 'high traffic' filegroup
(as well as the clusterd index), and the log onto yet another filegroup,
we significantly improve performance and reduce contention.
A couple of key considerations though. If you're running a single local
disk, filegroups don't help you much as windows still sees one disk.
You have to either have multiple disks or, in the case of a SAN,
multiple LUNs, that windows sees as separate disk and therefore manages
with separate 'channels'. Sometimes dealing with the infrastructure
guys can be a hassle, but not so much in our organization.
And I've never had an issue with restores that had anything to do with
filegroups, though I've never attempted a restore on only one filegroup.
Michael
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x54505626.[Email address protected]
Sent: Thursday, July 28, 2005 9:37 AM
To: LazyDBA Discussion
Subject: RE: Filegroups
My opinion, I never use filegroups.
There are lots of "implied" benefits of filegroups. However, I feel
unless you are on a small, slow, single disk server(like a desktop with
3 disks) I really don't see any benefit and see many pitfalls. For
example how can you backup by filegroup and restore by filegroup. What
does that do to referential integrity(unless a filegroup contains only
stagnant data) and
how many steps would it take to restore a full database? Separating
indexes from data etc. becomes less and less of an issue when you have
RAID drives and no issue on SAN storage. It just makes things more
complex and
harder to manage disk, etc.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Schultz Christine
[mailto:mssqldba-ezmlmshield-x78433670.[Email address protected]
Sent: Thursday, July 28, 2005 9:29 AM
To: LazyDBA Discussion
Subject: RE: Filegroups
What is the benefit of putting indexes in a different file group.
I've use file groups to "organize" subject areas within a Data
Warehouse, but have never had the luxury of having a big enough server
to assign a file group to a separate disk (or set of disks). Isn't the
benefit of file groups related to disk and separating the read/writes
per file group / disk??
-----Original Message-----
From: Trevor Weir
[mailto:mssqldba-ezmlmshield-x41143276.[Email address protected]
Sent: Wednesday, July 27, 2005 5:17 PM
To: LazyDBA Discussion
Subject: RE: Filegroups
Each file group is specific to one database.
1. Back up the database.
2. In Enterprise Manager, create separate file groups for indexes and
data (you can even split them into groups like datagroup1, datagroup2,
index1,index2, etc) by right-clicking a database, Properties,
Filegroups.
3. Go to the Data Files tab and create 1 file for each file group (you
can have multiples, but if your db is set to auto grow, the db won't
expand until ALL files in the file group that needs to grow are full).
4. Double-click each table you want to relocate, click the properties
button (2nd from the left next to the save button"), and move it to the
appropriate filegroup. Depending on the size of the table, it could take
a while. Once you tell it to save the changes DO NOT INTERRUPT IT.
5. To move the indexes, right-click a table, go to all tasks, then
manage indexes. Edit each index you want moved and change the file
group. Once again, DO NOT INTERRUPT IT.
Trevor
-----Original Message-----
From: Chris Wood
[mailto:mssqldba-ezmlmshield-x91690729.[Email address protected]
Sent: Wednesday, July 27, 2005 11:30 AM
To: LazyDBA Discussion
Subject: Filegroups
Hi,
I have an instance of sql server that is giving performing poorly, I
have already moved the database log file to another drive and checked
that the correct indexes are in place & the server has the correct
setup.
The main logical data drive is now 190gb big (over 140g of which is
free) this obviously made of up of a number of physical drives within
the Raid 5 set. Do I have to get the network guys to split this logical
drive into a number of logical drives before I can separate the single
datafile into a number of filegroups and place them on separate disks to
gain performance?
Does sql server put objects onto the primary filegroup unless specified?
how do I move some of this data across a number of filegroups to try and
improve performance.
----------------------------------------------------------------------
Scope is a registered charity number 208231.
Visit our website at http://www.scope.org.uk
This message, and any file(s) transmitted with it are confidential and
are intended only for the person(s) to whom they have been addressed by
the sender. This message may contain confidential and/or privileged
material. If you are not the intended recipient of this message, or if
you believe it was transmitted to you in error, you are required to
delete the message and any copies of it, and to notify the sender
immediately. Any unauthorised disclosure, copying, distribution, or
printing of this message or accompanying files, or unauthorised use of
any information contained therein, by anyone other than the intended
recipient(s) is prohibited and may be unlawful.
Any views expressed in this message or in any file(s) transmitted with
it are those of the author, and may not necessarily represent the views
of Scope.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the person or entity to which it is addressed. Any
review, retransmission, dissemination, or taking of any action in
reliance upon this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page