Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-12-31 : 02:39:52
|
| Hi everyone,I have few questions on File groups. I would really appreciate if someone can shed some light on those.File Group – in 70-431 MS text book – it says if I spread the database objects across several file groups, performance will be better. Question 1 – Would that be true even if the file group has several files and all the files reside in the same Drive? Will the performance be better?Question 2 - How do I spread the database objects across the data files? In other words, what does “spreading the database objects across different files mean?. Please look at the following script. It is just creating a mdf file (primary data file) and 3 secondary data file. In this scenario? When the data grows, it will fill ProjectPrimary.mdf first, then ‘ProjectData1.ndf', and then ‘ProjectData2.ndf' and so on. Am I right?What should I do to make sure I am separating certain objects to certain file groups? E.g. user data in one, database catalogue in one (this always should be in Primary), indexes in another, etc.Question 3 – If I don’t separate the DB objects across different file groups, but have it just like how the below script does, would it still increase the performance? (in both scenarios – the files in different drives, all the files are in the same drive.Question 4 – Why do we need file groups at all? If the files are clearly defined with a logical name and the physical location, what is the need for us to group different files to a logical group called File group?Question 5 – Why does data files need to be grouped into a file group but the log files don’t have a file group?. I read that it is because the log files are stored serially, but i don’t understand it. I guess if I get an answer to question 4, I will understand this as well. CREATE DATABASE ProjectsONPRIMARY(NAME = ProjectPrimary,FILENAME = 'C:\Projects_Data\ProjectPrimary.mdf',SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),FILEGROUP ProjectsFG( NAME = ProjectData1,FILENAME = 'C:\Projects_Data\ProjectData1.ndf',SIZE = 200MB,MAXSIZE = 1200,FILEGROWTH = 100),( NAME = ProjectData2,FILENAME = 'C:\Projects_Data\ProjectData2.ndf',SIZE = 200MB,MAXSIZE = 1200,FILEGROWTH = 100),FILEGROUP ProjectsHistoryFG( NAME = ProjectHistory1,FILENAME = 'C:\Projects_Data\ProjectHistory1.ndf',SIZE = 100MB,MAXSIZE = 500,FILEGROWTH = 50)Thanks for your time..Regards,Shiyam |
|
|
sriram_ys
Starting Member
3 Posts |
Posted - 2010-12-31 : 03:24:03
|
| Filegroups and Files at large are mainly used for two reasongs...1. Expand the database beyond it current capacity in the current disk.2. Improve the performance.Question 1: There is no major advantage as they are all on the same disk. spreading across the disks will help.Question 2. Actually you need to specify on what goes where, which tables go to which file. which indexes go to which file.Should all my read only tables be together at a place. question 3.All the files on differernt drives, it defintely will better performance, if they are on the same drive, may or may not, depends on the I/O ,reads and writes.question 4. Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.(BOL)Question 5.The sql server uses only a single log file at a time for database to write all the log operations.The below link may help you further.http://msdn.microsoft.com/en-us/library/ms179316.aspx |
 |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-12-31 : 11:28:12
|
| Thanks Sriram.Can you let me how the command would be to specify the followings:==================================================Actually you need to specify on what goes where,which tables go to which file.which indexes go to which file.==================================================- Shiyam |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-31 : 11:47:03
|
| When you create the object you specify it. That's what the ON [PRIMARY] that you see in generated scripts is, a reference to the Primary filegroup.--Gail ShawSQL Server MVP |
|
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-12-31 : 12:15:24
|
| Thanks Gail. I get it.So, if a table should belong to primary, I would create a table like this - CREATE TABLE projects.dbo.customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)ON FILEGROUP ProjectsFGSo, this table will be created on the file group "ProjectsFG". Correct?I have a uestionsI have a Database in Production. It has a primary "File group" on D drive and no Secondary files. It has about 600 tables, and about 100 indexes.. I can add new hard drives, alter database and add new files and file groups.. can i move some tables and indexes to new file group ? what would be the process?thanks a lot |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-12-31 : 12:35:41
|
| No. Just ON <Filegroup name>, exactly as I posted, just replacing PRIMARY with the name of the intended filegroup.See Books Online for syntaxTo move indexes you need to do CREATE INDEX ... WITH DROP_EXISTING and specify the filegroup. Do that with the clustered index and you've moved the table.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Questions on File Groups