March 2, 2009 at 9:22 am
I'm looking into implementing filgroups for this disasterously slow custom CRM that we are having. It is on SQL2005 Standard Edition. The original database set-up was bad and the disk it the bottleneck.
I'm pretty set on using filegroups and am currently looking on how I will be distributing the data. While I understand that you can create many secondary filegroups and create the .ndf files within those groups, there seems to be no control at the file level for allocating objects. I see that you can re-assign tables and indexes to different filegroups but does SQL Server decide on how to assign which resources go into the physical files withn that filgroup? Can you get more granular than that? If I create 5 .ndf files in a specific filegroup, I'd like to be able to assign objects to each of those files ( as in let's say 1 file for the base table/clustered index and 4 files corresponding to 4 non-clustered indexes). From what, I've read so far, that level of granularity is not offered.
Thanks.
March 2, 2009 at 9:30 am
I suppose I found an answer but please feel free to comment:
"SQL Server fills the database files in a filegroup proportionally. This means if you have two data files, one with 200MB free and one with 400MB free, SQL Server will allocate one extent from the first file and two extents from the second file when writing data. In this way, you can eliminate hot spots and reduce contention in high-volume environments."
March 2, 2009 at 10:06 am
It's a round robin type thing.
You can do one file / filegroup and get this granuarity.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply