SQL 2000 Physical Layout Question

[rc]

Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks

[Ray]

Not really. Although there many be some small gain in having multiple files
in file group. At least equal to the number of processors in the server. I
would recommend the following layout for data and index.

Assuming four processors, create four files in your primary file group to
hold the data. Preallocate the files as ((the size of the table + growth) *
2)/4 this should give you enough room to grow and reindex the table. Create
a second file group for the nonclustered indexes. This way if you ever want
to move the index file group off to another set of disks you're ready.

"rc" <[e-mail]> wrote in message
news:v0******** *************** *********@4ax.c om...

Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks

[joshsackett]

Hi rc,
Ray is on the right track with his solution. However, with a 90 million
row table you may have to do something more dramatic in order to
increase performance. I suggest you look into creating a partitioned
view (check it out in Books Online).

Let's assume your 90 million row table has its clustered index based of
the key "Social Security Number" and your application does MOST of its
searches using this key. You can split the single table into 10 smaller
tables (or more or less) using the SSN as your guide. Any SSN that
starts with 0 goes into one table, 1 to another table, 2 to another and
so on. Finally you create a view that performs a "select *" from each
table - the syntax can be found in BOL.

Now any query only needs to perform an inital search to see in which
table your record is located and then it performs a search on 1/10 the
amount of the original rows (assuming an event dispersement of SSNs).

[rc]

On 4 Feb 2005 11:43:40 -0800, "joshsacket t" <jo*********@gm ail.com>
wrote:

Hi rc,
Ray is on the right track with his solution. However, with a 90 million
row table you may have to do something more dramatic in order to
increase performance. I suggest you look into creating a partitioned
view (check it out in Books Online).

Let's assume your 90 million row table has its clustered index based of
the key "Social Security Number" and your application does MOST of its
searches using this key. You can split the single table into 10 smaller
tables (or more or less) using the SSN as your guide. Any SSN that
starts with 0 goes into one table, 1 to another table, 2 to another and
so on. Finally you create a view that performs a "select *" from each
table - the syntax can be found in BOL.

Now any query only needs to perform an inital search to see in which
table your record is located and then it performs a search on 1/10 the
amount of the original rows (assuming an event dispersement of SSNs).

Thanks both of you, I will have a think about the info provided

[Gang He [MSFT]]

What really matters here is the number of disk spindles for the filegroup
containing the 50GB table. The more disk spindles it got, the more disks
the server can keep busy by issuing readahead IOs, and the better the
performance of the scans over the table. So try to add more physical disks
to the filegroup.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"rc" <[e-mail]> wrote in message
news:v0******** *************** *********@4ax.c om...

Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks

[Jens]

You might also wan´t to look in to putting the indexes(non-clustered)
on a seperate filegrou, residing on a seperate physical disk. That was
the server kan access index - and table data simultaneusly.. . might
speed things up as well