June 15, 2012 at 4:20 am
a vendor has implemented a change to their db structure to split the database (700GB) into multiple datafiles (8) which we have now placed on 2 separate SAN disks (4 datafiles per disk). i would like to know how SQL manages data across these datafiles. Does SQL move whole tables to separate datafiles or can tables exist in multiple datafiles? can you manually move a table to a specific datafile?
thanks for any info.
June 15, 2012 at 4:33 am
Does SQL move whole tables to separate datafiles
No. SQL may move a table to different data files (beloging to same FILEGOUP)
can tables exist in multiple datafiles?
Yes. A table can exist on multiple data files. Multple tables can exist on a single data file.
can you manually move a table to a specific datafile?
Yes. You can, using FILEGROUPS
June 15, 2012 at 4:42 am
thanks for the info suresh.
we have only one filegroup. does that mean i cannot move a table between datafiles in a single filegroup?
how does SQL manage data in these new datafiles after they are initially created? i see that the #1 datafile has 12% freespace, #2 has 13%, #3 has 12%, #4 has 12%, and #5-8 has 90%. so it seems that, at least initially, SQL is primarily using the first 4 datafiles and hardly using the last 4. is there any way to more evenly spread the data across these datafiles?
June 15, 2012 at 4:47 am
No. You cannot move a table between datafiles in a single filegroup.
SQL Server uses proportionate fill algorithm. That is, data would be spread evenly accoss the data files.
June 15, 2012 at 4:48 am
No. You cannot move a table between datafiles in a single filegroup.
SQL Server uses proportionate fill algorithm. That is, data would be spread evenly accoss the data files.
June 15, 2012 at 4:50 am
that's what i was thinking - that sql automatically spreads the data across datafiles.
many thanks!
June 15, 2012 at 6:49 am
OLDCHAPPY (6/15/2012)
a vendor has implemented a change to their db structure to split the database (700GB) into multiple datafiles (8) which we have now placed on 2 separate SAN disks (4 datafiles per disk).
They added the files alongside the existing presumably.
OLDCHAPPY (6/15/2012)
thanks for the info suresh.we have only one filegroup. does that mean i cannot move a table between datafiles in a single filegroup?
how does SQL manage data in these new datafiles after they are initially created? i see that the #1 datafile has 12% freespace, #2 has 13%, #3 has 12%, #4 has 12%, and #5-8 has 90%. so it seems that, at least initially, SQL is primarily using the first 4 datafiles and hardly using the last 4. is there any way to more evenly spread the data across these datafiles?
Oh dear, this is what happens when people mess with the file layouts when there's probably nothing wrong with them. The 1 file per cpu hangon is totally dead and generally not even relevant for later versions of SQL Server.
The proportionate fill algorithm is hosed as the data files likely all have differing sizes and growth rates. The only way to ensure that it starts out properly is to create a new filegroup with the desired number of files and then move the tables\indexes to the new file group.
All data files must have the same initial size and growth rates for proportionate fill to be useful. They should have just left it alone. You'll also see people doing this for t-log files too (I have in the past).
As already mentioned you cannot move an object to a particular data file, unless that data file is in a filgroup of it's own. SQL Server works with objects at the filegroup level
Post back the results of the following query
selectdf.name AS LogicalFileName
, isnull(fg.name, 'Log') AS FilegroupName
, physical_name AS PhysicalOSName
, (df.size * 8 / 1024) AS SizeMBs
, case df.max_size
when 0 then 'No Growth'
when -1 then 'Unlimited'
when 268435456 then '2TB'
else cast(df.max_size / 128 AS VARCHAR(10)) + ' MBs'
end AS MaxFileSize
, case df.is_percent_growth
when 0 then cast(df.growth / 128 AS VARCHAR(10)) + ' MBs'
else CAST(df.growth AS VARCHAR(10)) + ' %'
end AS Growth
from sys.database_files df left outer join sys.filegroups fg ON df.data_space_id = fg.data_space_id
--where df.type = 0
order by df.type
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply