Re: Spread table.

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.


.

Relevant Pages

  • Re: Spread table.
    ... > multiple files unless each file is on a separate drive array. ... > Raid 10 and with multiple processors Sql Server can use multiple threads to ...
    (microsoft.public.sqlserver.clients)
  • Re: fread, explode, and eof problem
    ... quotes from multiple text files in a directory, ... to separate into an array of names and quotes. ...
    (comp.lang.php)
  • fread, explode, and eof problem
    ... I have a 'random quotes' plugin that I use which reads tab delimited ... quotes from multiple text files in a directory, ... to separate into an array of names and quotes. ...
    (comp.lang.php)
  • Bug+fix: PDC20271 RAID detection fails
    ... My array was not detected by my kernel. ... the PDC RAID superblock, that is located at the start ... of the last track on the disk. ... is a multiple of track size and if not, ...
    (comp.os.linux.hardware)
  • Re: Another Filegroup question
    ... I would suggest multiple filegroups only so it would be easier to ... small array as this I don't think multiple files will buy you anything. ... don't think you have too much of a choice if you only have 4 drives that are ...
    (microsoft.public.sqlserver.server)
Loading