Files not filing with data

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high
contention on this file. We added 3 more files to match the processor
number. The problem is that the 3 additional files are not filling with
data. Does anyone know why this happens or can reccommend a fix?
--
willHi Will
Did you add the new files to the same file group or did you create a new
group?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will
>|||Hi
Since all your tables are in the 1st file group, SQL Server will continue to
use the 1st file group until it is full.
It does not balance over the files. You need to specifically move a table or
Index onto a file group for it to be used immediately.
Unless each file group is on a separate disk system (or LUN on a SAN), it
will not help adding file groups as the same IO contention continues to
exists.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will|||Let me correct myself:
We have a file group called 'Avail'.
In That file group we had all the tables running on 1 file.
We added 3 additional files to that filegroup and noticed they were not
filling with data. Has anyone seen this before?
--
will
"Kalen Delaney" wrote:
> Hi Will
> Did you add the new files to the same file group or did you create a new
> group?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> > We have a quad sql server that runs OLTP transactions at the rate of
> > 100's per second (read & Write).
> >
> > We used to have all the tables on 1 file but started to notice high
> > contention on this file. We added 3 more files to match the processor
> > number. The problem is that the 3 additional files are not filling with
> > data. Does anyone know why this happens or can reccommend a fix?
> > --
> > will
> >
>
>|||If a table is created on a filegroup, and that filegroup has multiple files,
all the files should be used as more data is inserted into the table.
Are you seeing that existing tables are not seeming to use the new files?
How are you determining that?
Can you try creating a new table on the filegroup and see if its data is
spread around?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> Let me correct myself:
> We have a file group called 'Avail'.
> In That file group we had all the tables running on 1 file.
> We added 3 additional files to that filegroup and noticed they were not
> filling with data. Has anyone seen this before?
> --
> will
>
> "Kalen Delaney" wrote:
>> Hi Will
>> Did you add the new files to the same file group or did you create a new
>> group?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "we7313" <we7313@.discussions.microsoft.com> wrote in message
>> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
>> > We have a quad sql server that runs OLTP transactions at the rate of
>> > 100's per second (read & Write).
>> >
>> > We used to have all the tables on 1 file but started to notice high
>> > contention on this file. We added 3 more files to match the processor
>> > number. The problem is that the 3 additional files are not filling
>> > with
>> > data. Does anyone know why this happens or can reccommend a fix?
>> > --
>> > will
>> >
>>
>>
>|||Yes the existing table is not using the new files in the file group.
If I go into enterprisemanager/view/taskpad I can see how big the data files
are and much data is actually in them. What I'm seeing is that 99% of the
data continues to go into the original file. I do see about 1% of data going
to the other 3 files combined.
--
will
"Kalen Delaney" wrote:
> If a table is created on a filegroup, and that filegroup has multiple files,
> all the files should be used as more data is inserted into the table.
> Are you seeing that existing tables are not seeming to use the new files?
> How are you determining that?
> Can you try creating a new table on the filegroup and see if its data is
> spread around?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> > Let me correct myself:
> > We have a file group called 'Avail'.
> > In That file group we had all the tables running on 1 file.
> > We added 3 additional files to that filegroup and noticed they were not
> > filling with data. Has anyone seen this before?
> > --
> > will
> >
> >
> > "Kalen Delaney" wrote:
> >
> >>
> >> Hi Will
> >>
> >> Did you add the new files to the same file group or did you create a new
> >> group?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> >> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> >> > We have a quad sql server that runs OLTP transactions at the rate of
> >> > 100's per second (read & Write).
> >> >
> >> > We used to have all the tables on 1 file but started to notice high
> >> > contention on this file. We added 3 more files to match the processor
> >> > number. The problem is that the 3 additional files are not filling
> >> > with
> >> > data. Does anyone know why this happens or can reccommend a fix?
> >> > --
> >> > will
> >> >
> >>
> >>
> >>
> >>
> >
>
>