July 12, 2012 at 12:39 am
Hi,
For adding data file & index file on default primary file group
Currently datafile.MDF size 200GB and associated with primary file.
1. Adding datafile - Please suggestion me which is best method.
-------------------
I want create another datafile2.NDF, By default It will create primary file group only.
Here, should I use primary file group or create secondary file group?
USE [master]
GO
ALTER DATABASE [DBName] ADD FILEGROUP [SECONDARY]
GO
ALTER DATABASE [DBName] ADD FILE
( NAME = N'datafile2', FILENAME = N'D:\mssql\datafile2.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]
GO
2. Adding index file
--------------------
I want to create separate index file group, should I use separte filgroup for indexing?
USE [master]
GO
ALTER DATABASE [DBName] ADD FILEGROUP [Index]
GO
ALTER DATABASE [DBName] ADD FILE
( NAME = N'Index1', FILENAME = N'D:\mssql\Index1.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Index]
GO
thanks
July 12, 2012 at 1:04 am
Firstly, you seem to have a misunderstanding of files and filegroups, take a look in books online and familiarise yourself with them. Now to cover your questions
ananda.murugesan (7/12/2012)
1. Adding datafile - Please suggestion me which is best method.-------------------
I want create another datafile2.NDF, By default It will create primary file group only.
Here, should I use primary file group or create secondary file group?
USE [master]
GO
ALTER DATABASE [DBName] ADD FILEGROUP [SECONDARY]
GO
ALTER DATABASE [DBName] ADD FILE
( NAME = N'datafile2', FILENAME = N'D:\mssql\datafile2.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]
GO
Why are you adding a new data file to the primary filegroup why not just increase growth on the existing file. When SQL server encounters multiple files in a filegroup it will attempt to round robin the writes to the files. This will obviously not work at all when one file is 200gb empty and the other is 200gb full!!!!
ananda.murugesan (7/12/2012)
2. Adding index file--------------------
I want to create separate index file group, should I use separte filgroup
If you want to separate your indexes you will have to create a new filegroup. Objects are created on filegroups in SQL server not on individual files. Are the filegroups going o be placed on separate storage mediums, if not then there's not much benefit apart from added admin overhead.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 12, 2012 at 3:13 am
First question, why are you splitting the files apart?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2012 at 3:29 am
To add to Perry, is you want to separate indexes, you need separate physical drives. Not separate drive letters or LUNs, but physical drives if you are looking for performance increases. However please review what Perry linked to, and also answer Gail's question.
July 12, 2012 at 5:02 am
Thanks you so much for reply from SQL Server Genius..GilaMonster,Steve Jones and Perry Whittle
1. If more then 200GB primary data file reached, I assumed need to create one more datafile for improve database perfromance? this doubt would be cleared by reply Mr. Perry Whittle, multiple datafiles round robin will happend with SQL transaction also database performance slow.
please suggestion me, which Situation need to create another datafile?
2. Index data file, we can maintined index on sperate files if database size more than 500GB level. Index data file created with on same logical drive which is placed datafile MDF location.
thanks
July 12, 2012 at 6:51 am
ananda.murugesan (7/12/2012)
1. If more then 200GB primary data file reached, I assumed need to create one more datafile for improve database perfromance?
No.
There's no xGB 'limit' on the size of a single data file for performance reasons. (the only limit is the 16TB max size limit).
Splitting for performance reasons requires a lot more than just adding a second file and hoping it'll work. Needs detailed analysis to ensure that the DB is IO bottlenecked, careful design of what gets split to where, and completely seperate IO channels for the resultant files.
Otherwise it'll probably be a waste of time and may even degrade performance.
This is very different from splitting for recovery purposes, which is the other main reason to split to multiple files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2012 at 7:15 am
ananda.murugesan (7/12/2012)
1. If more then 200GB primary data file reached, I assumed need to create one more datafile for improve database perfromance?
That assumption is incorrect.
ananda.murugesan (7/12/2012)
2. Index data file, we can maintined index on sperate files if database size more than 500GB level. Index data file created with on same logical drive which is placed datafile MDF location.thanks
Creating the index filegroup and its files on the same location as the primary filegroup and its files will not help performance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2013 at 3:36 am
HI,
Can any one suggest me how to place only fulltext index into diffrent filegroup which i created in diffrent driv.
I need that syntax or the meth
February 1, 2013 at 3:45 am
Please ask new questions in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply