October 3, 2011 at 2:18 pm
Hello all, We migrated from an older server with 3 logical drives which had MDF, NDF1, NDF2 and NDF3. Our new Server has 6 logical drives, so we had to create additional NDF4, NDF5 and NDF6 files in order to utilize all drives for performance. When we did our Restore, all the data is on the NDF1, NDF2 and NDF3 and hardly any data is being put on NDF4, NDF5 and NDF6.
What is the best way to spread the data among all the files or a quicker way for SQL to fill up the rest of the NDF files.
Let me know if you need additional information or if I am not asking my question right. Thanks
October 3, 2011 at 3:53 pm
Just because you have extra drives doesnt mean you have to create extra files. Are all these files in the same filegroup or separate filegroups? I'm guessing that they're in separate filegroups
Object locations are specified during creation, for instance the following creates a table called mytable on a filegroup called INDEXES
CREATE TABLE mytable(
id int
, name varchar(10)
)
ON INDEXES
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 3, 2011 at 3:55 pm
To be honest I think your approach is probably not the best.
For big database I can definitely see it, but for smaller databases I generally break then up manually, meaning that high utilization database are generally placed on volumes seperate from other high utilization databases. Using SQL Monitor (a product for our host Red-Gate) I can easily see utilization of the drives. I spread out that utilization accross several drives and increased performance by having less overall contention.
As an answer to your question I am guessing that it is tied to the fact that your data files are not fixed in size and are auto-grow, I believe that if they are all sized the same AND in the same file group that they are more likely to fill *more* uniformly, which is not to say completely uniformly.
CEWII
October 5, 2011 at 10:32 pm
dbdmora (10/3/2011)
What is the best way to spread the data among all the files or a quicker way for SQL to fill up the rest of the NDF files.
If your table has clustered index, re-create the clustered index specifying the new filegroup as the target. By doing this your table will move to the new filegroup.
If your table doesnt have clustered index, follow the same steps and drop clustered index after movement.
ps- you cannot move a table to a specific file. You can move to a filegroup. If a file group has multiple files, they are proportionately filled.
Having said that, why not consider moving tempdb to dedicated drives? Log files of most active databases?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply