Filegroups - continued
-
Hello everyone,
I am new to the forum as well as SQL and was hoping to get some additional ifnormation on Filegroups. Can someone please let me know fi my understanding of this topic is accurate?
1. Filegroups allow you to logically organize data files
2. A best practice would be to create a new FG for each data file so that the original primary FG only contains the catalog for the DB
3. Another best practice would be to ensure you have created all FG's ahead of tiem so and make sure all new objects you create go in to the desired FG during the creation process.
Question:
When it comes to secondary data files, what determines how many you create? What si the actual benefit of creating multiple data files if you only have one physical disk in the server? (I know this is very unlikely and not recomended at all, my reason for asking is that I'm trying to establish why you create multiple ndf files) It it is totally dependant on the disk configuration of the server so that you can take advantage of paralell processing, then I believe I know the answer to this one.
In SMSS how do you move an existing table (or other object) in to a newly created file group?
Any assistance would be greatly appreciated.
Thank you all,
Bob
-
It could also depend on high large your database is expected to get. Using filegroups can speed up backup processes by allowing you to backup only the filegroups where data is changing, for instance.
-
I'd not create more filegroups than necessary. I'd create enough to logically group the associated tables and that would be it.
As for the number of files, I was at Microsoft today and a general rule of thumb is half of the logical number of CPU's.
I must admit, aside from tempdb (ours has 8 data files), our databases have a maximum of 2 data files.
-
To move an existing table in to a newly created file group:
Right click the table and select design. Under propeties and table designer, you can select the new filegroup.
-
Sometimes I create multiple file groups on a single physical drive if there's a possibility of adding drives in the future. Then the upgrade to take advantage is simple.
Also, I find it interesting (sad, I know) to see exactly how much space each logical area consumes. For instance, I often have a file group for tables used temporarily by an importer (load or staging tables) and know exactly how much data is typically imported each time. You can estimate it from the table spec * number of rows, but my maths sucks. 🙂 Plus, you can shrink just those files afterwards.
Chris
.
-
The big advantage of creating file groups on different disks is purely physical... you get more read write heads involved. Of course, the same could be said for partitioning one file group over several disks... more read write power with less sequential seek time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply