October 19, 2009 at 7:16 pm
What is the algorithm/logic SQL Server uses when you create an index or table on a filegroup that has more than one file.
Which exact file will it fall under?
For example the File Group SECONDARY has 2 Files: FirstFile.ndf and SecondFile.ndf
CREATE NONCLUSTERED INDEX IX_WorkNumber_WorkNumberID
ON WorkNumber(WorkNumberID)
ON SECONDARY;
Where will this index end up at : FirstFile.ndf or SecondFile.ndf?
Likewise, if you created a table on this filegroup where does it land in?
October 19, 2009 at 7:53 pm
Very interesting question...
Well, according to my knowledge, SQL Server uses round robin procedure. When you try to insert the data into a table, it would use the 1st datafile until it gets filled and then starts to use the 2nd datafile until that gets filled up, then if needed some more space, the 1st datafile is selected and it is grows the 1st datafile as mentioned by its properties and so on.
but according to your question about in which datafile does the table gets created I guess,it would be in the 1st datafile (if there was enough space in it when you created the table).
Hope some one can correct if I am wrong.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 19, 2009 at 10:33 pm
SQL uses a proportional fill algorithm if there are two data files in a filegroup. So if there are two files (the same size), both empty, the table/index will be half in one, half in the other.
If there are two files, both empty, one 100GB and the other 200GB, then the table/index will be 1/3 in the first, 2/3 in the second.
http://msdn.microsoft.com/en-us/library/ms187087.aspx
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
October 19, 2009 at 10:35 pm
Bru Medishetty (10/19/2009)
Well, according to my knowledge, SQL Server uses round robin procedure.
If there are multiple log files, they are used in a round-robin approach, but not the data 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
October 20, 2009 at 5:57 am
Gail
Thx for ur input on log vs data file usage, when more than 1 file is in use.
I have 2 data files. One is 201 MB full. The other one is 5GB.
I would like to make sure everything gets moved to File with 5GB, and then delete the file with 201MB Full.
How could I go about that?
Thanks
Dan
October 20, 2009 at 6:58 am
If you have 2 Files and if both of them are in the same filegroup, then shrinking the 201 MB File and emptying it is the option to go.
USE [DatabaseName]
GO
DBCC SHRINKFILE (N'Datafile2' , EMPTYFILE)
GO
ALTER DATABASE [DatabaseName] REMOVE FILE [Datafile2]
GO
This is ok if you have only 2 datafiles you mentioned, if you have 3rd File in the same filegroup, I am not sure how to move it to a specific file but would have a workaround by disabling the autogrowth of the 3rd file and do the same exercise and data would be moved to the 1st File
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 20, 2009 at 7:02 am
After I read that 'EMPTYFILE option migrates all data from the specified file to other files in the same filegroup' from http://msdn.microsoft.com/en-us/library/aa258824%28SQL.80%29.aspx, I was assured and did the shrink and delete, and that solved the issue.
Thanks Bru & Gail, mainly on the info on the algorithm.
October 20, 2009 at 7:41 am
Bru
I had sent my post before seeing yours. Thanks.
I had only 2 files to deal with. It will be interesting to check out the 3 file option sometime, to learn from it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply