Adding Data Files

I inherited a server with a database that has 3 data files in the primary
filegroup, but SQL Server is only writing to the first one. It looks like
the 2nd and 3rd files were not created when the database was created, but
were added on later. The initial data file is 164 Gb in size and the 2nd and
3rd are still 1 Mb each. Any suggestions on why SQL Server is only writing
to the 1st data file?
Thanks,
HariSQL Server writes the data in each file using a proportional fill algorithm.
This algorithm determines the amount of free space in each file and splits
the data based on the % of free space in each file. If the file is only 1MB
it has no free space compared to the 164GB file. This means all or most of
the data goes to that one. Make the files much larger and you will start to
see data migrate over as you add data. All data files in the same file group
should be the same size so the data is spread evenly across all of them. If
you increase the size and reindex you will see the data start to get more
proportional over time.
--
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
>I inherited a server with a database that has 3 data files in the primary
> filegroup, but SQL Server is only writing to the first one. It looks like
> the 2nd and 3rd files were not created when the database was created, but
> were added on later. The initial data file is 164 Gb in size and the 2nd
> and
> 3rd are still 1 Mb each. Any suggestions on why SQL Server is only
> writing
> to the 1st data file?
> Thanks,
> Hari|||Thanks. I had wondered if the mistake they made was in not making the
initial file size on the second two files the same as the size of the initial
file - or at least larger than 1 Mb.
I'm trying to maintain this server until I can upgrade it to SQL Server 2005
and migrate to a more suitable environment. Unfortunately, the server has
only a single RAID 5 array to place all of the data and log files on and
performance is a real problem. Is there any performance benefit to having
multiple data files in a database when all of them are going to be located on
the same physical drives anyway? Also, is there any benefit to placing them
on different logical partitions on the array or does the fact that they're
still on the same physical array negate any benefit?
Hari
"Andrew J. Kelly" wrote:
> SQL Server writes the data in each file using a proportional fill algorithm.
> This algorithm determines the amount of free space in each file and splits
> the data based on the % of free space in each file. If the file is only 1MB
> it has no free space compared to the 164GB file. This means all or most of
> the data goes to that one. Make the files much larger and you will start to
> see data migrate over as you add data. All data files in the same file group
> should be the same size so the data is spread evenly across all of them. If
> you increase the size and reindex you will see the data start to get more
> proportional over time.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:AE0BAAC1-7BF5-4F0A-99E0-20E0DE0929B0@.microsoft.com...
> >I inherited a server with a database that has 3 data files in the primary
> > filegroup, but SQL Server is only writing to the first one. It looks like
> > the 2nd and 3rd files were not created when the database was created, but
> > were added on later. The initial data file is 164 Gb in size and the 2nd
> > and
> > 3rd are still 1 Mb each. Any suggestions on why SQL Server is only
> > writing
> > to the 1st data file?
> >
> > Thanks,
> > Hari
>
>|||I would have thought you would be better off using emptyfile and dropping
them.
I can't think of a benefit.
Raid 5 is generally slow on writes and will probably be hurting your logfile
most.
If you are using a battery backed up raid controller you could try
dedicating the cache 100% to writes and see if that helps.
There always a risk with caching writes but if there's a battery there it is
minimised.
If you could get the budget to get an extra pair of disks as a mirror for
the log you should get a decent benefit.
Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing.
Try pointing out the the purse holders that if a disk failed on raid 5 the
performance while running in phantom mode would probably bring the machine
to it's knees.
Paul|||I agree - I would prefer to just delete them if there's no benefit to having
them, but wanted to verify it first.
I probably should have gone into a little more detail about how the database
is used. We do a bulk insert once a day and the rest of the time it's used
for reads only, so the transaction logs aren't much of a factor in this case.
The tables are rather large and the only reason to keep the extra files
would be if it would help speed up queries. The server has a dual core
processor if that makes any difference. This is SQL Server 2000 Standard
Edition running on a Windows 2003 server.
We have new servers and lots of hard drives on order, so I just need to
tread water for a little longer.
Thanks,
Hari
"Paul Cahill" wrote:
> I would have thought you would be better off using emptyfile and dropping
> them.
> I can't think of a benefit.
> Raid 5 is generally slow on writes and will probably be hurting your logfile
> most.
> If you are using a battery backed up raid controller you could try
> dedicating the cache 100% to writes and see if that helps.
> There always a risk with caching writes but if there's a battery there it is
> minimised.
> If you could get the budget to get an extra pair of disks as a mirror for
> the log you should get a decent benefit.
> Even a pair of IDE/SATA with NT s/w mirroring would be better than nothing.
> Try pointing out the the purse holders that if a disk failed on raid 5 the
> performance while running in phantom mode would probably bring the machine
> to it's knees.
> Paul
>
>|||It's query tuning and sneaking some extra memory in till then. Bear in mind
that some of your queries may be writing to tempdb.
We keep our tempdb on separate spindles. From what I have read, 2005 makes
much heavier use of tempdb especially with the new isolation levels (Row
level versioning).
Interesting little article by Tony Rogerson.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx|||I agree with Paul in that for your current situation you may be better off
dropping those files altogether.
--
Andrew J. Kelly SQL MVP
"Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...
>I agree - I would prefer to just delete them if there's no benefit to
>having
> them, but wanted to verify it first.
> I probably should have gone into a little more detail about how the
> database
> is used. We do a bulk insert once a day and the rest of the time it's
> used
> for reads only, so the transaction logs aren't much of a factor in this
> case.
> The tables are rather large and the only reason to keep the extra files
> would be if it would help speed up queries. The server has a dual core
> processor if that makes any difference. This is SQL Server 2000 Standard
> Edition running on a Windows 2003 server.
> We have new servers and lots of hard drives on order, so I just need to
> tread water for a little longer.
> Thanks,
> Hari
> "Paul Cahill" wrote:
>> I would have thought you would be better off using emptyfile and dropping
>> them.
>> I can't think of a benefit.
>> Raid 5 is generally slow on writes and will probably be hurting your
>> logfile
>> most.
>> If you are using a battery backed up raid controller you could try
>> dedicating the cache 100% to writes and see if that helps.
>> There always a risk with caching writes but if there's a battery there it
>> is
>> minimised.
>> If you could get the budget to get an extra pair of disks as a mirror for
>> the log you should get a decent benefit.
>> Even a pair of IDE/SATA with NT s/w mirroring would be better than
>> nothing.
>> Try pointing out the the purse holders that if a disk failed on raid 5
>> the
>> performance while running in phantom mode would probably bring the
>> machine
>> to it's knees.
>> Paul
>>|||That's what I'm going to do. Thanks for the tips!
Hari
"Andrew J. Kelly" wrote:
> I agree with Paul in that for your current situation you may be better off
> dropping those files altogether.
> --
> Andrew J. Kelly SQL MVP
> "Hari Seldon" <HariSeldon@.discussions.microsoft.com> wrote in message
> news:C12EE151-5272-4697-B057-E773C64AADDD@.microsoft.com...
> >I agree - I would prefer to just delete them if there's no benefit to
> >having
> > them, but wanted to verify it first.
> >
> > I probably should have gone into a little more detail about how the
> > database
> > is used. We do a bulk insert once a day and the rest of the time it's
> > used
> > for reads only, so the transaction logs aren't much of a factor in this
> > case.
> > The tables are rather large and the only reason to keep the extra files
> > would be if it would help speed up queries. The server has a dual core
> > processor if that makes any difference. This is SQL Server 2000 Standard
> > Edition running on a Windows 2003 server.
> >
> > We have new servers and lots of hard drives on order, so I just need to
> > tread water for a little longer.
> >
> > Thanks,
> > Hari
> >
> > "Paul Cahill" wrote:
> >
> >> I would have thought you would be better off using emptyfile and dropping
> >> them.
> >> I can't think of a benefit.
> >>
> >> Raid 5 is generally slow on writes and will probably be hurting your
> >> logfile
> >> most.
> >> If you are using a battery backed up raid controller you could try
> >> dedicating the cache 100% to writes and see if that helps.
> >> There always a risk with caching writes but if there's a battery there it
> >> is
> >> minimised.
> >>
> >> If you could get the budget to get an extra pair of disks as a mirror for
> >> the log you should get a decent benefit.
> >> Even a pair of IDE/SATA with NT s/w mirroring would be better than
> >> nothing.
> >>
> >> Try pointing out the the purse holders that if a disk failed on raid 5
> >> the
> >> performance while running in phantom mode would probably bring the
> >> machine
> >> to it's knees.
> >>
> >> Paul
> >>
> >>
> >>
>
>