Re: Spread table.
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Thu, 31 Mar 2005 14:23:56 -0500
You create a new filegroup with 3 files in it. Place one file on each of
the 3 drives. Then create the table in that filegroup and when you populate
it sql server will spread the data evenly across the 3 files. That is
assuming the files are all the same size to begin with. You can not objects
such as tables or indexes in a specific file but you can place them in a
filegroup. SQL Server will split them up between the files in that
filegroup for you.
--
Andrew J. Kelly SQL MVP
"Rogers" <Rogers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2577DFD2-050E-451A-8341-C0D4E9BBFDA9@xxxxxxxxxxxxxxxx
> Thanks for your reply but the question is how can I set one table into
> separate file and I will devide that files into three different physical
> drive but I wanna know how can I split one table into three different
> files.
>
> Thanks
>
> "Andrew J. Kelly" wrote:
>
>> This is not true for SQL 2000 and above. It used to be in 7.0 that a
>> single
>> thread was used per file. In 2000 it can use multiple threads on a
>> single
>> file. 10 million rows is not a lot and you don't gain much by creating
>> multiple files unless each file is on a separate drive array. Not a
>> separate Logical drive but a physical drive. But on a fast array such as
>> a
>> Raid 10 and with multiple processors Sql Server can use multiple threads
>> to
>> read a single file in parallel.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "Rogers" <Rogers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:98DB88E9-DEFB-49AB-8DED-ED11C6F9A6B9@xxxxxxxxxxxxxxxx
>> > If your database is very large and very busy, multiple files can be
>> > used
>> > to
>> > increase performance. Here is one example of how you might use multiple
>> > files. Let's say you have a single table with 10 million rows that is
>> > heavily
>> > queried. If the table is in a single file, such as a single database
>> > file,
>> > then SQL Server would only use one thread to perform a sequential read
>> > of
>> > the
>> > rows in the table. But if the table were divided into three physical
>> > files
>> > (all part of the same filegroup), then SQL Server would use three
>> > threads
>> > (one per physical file) to sequentially read the table, which
>> > potentially
>> > could be much faster. In addition, if each file were on its own
>> > separate
>> > disk
>> > or disk array, the performance would even be greater.
>> >
>> > Can any one let me know how can I spread one large table into three
>> > different files.
>>
>>
>>
.
- References:
- Spread table.
- From: Rogers
- Re: Spread table.
- From: Andrew J. Kelly
- Re: Spread table.
- From: Rogers
- Spread table.
- Prev by Date: Re: Spread table.
- Next by Date: URGENT
- Previous by thread: Re: Spread table.
- Next by thread: URGENT
- Index(es):
Relevant Pages
|
