Performance Question

[rc@die@you@!spammers.sandworm.demon.co.uk]

Hi

We have a SQL server on Win2k. the physical size of the db is about
40G and the main table has approx 65m rows in it. At the moment the
entire database is on one data file. The entire server including the
OS is on a RAID 0 array with one RAID controller.

My question is would I get any performance benefit if I was to have
more than several data file rather than one big data file, bearing in
mind that there is only one disk controller.

I am mainly interested in read performance rather than write.

I am just starting out with MS SQL

Thanks for any info

[Greg D. Moore \(Strider\)]

Re: Performance Question


<rc@die@you@!sp ammers.sandworm .demon.co.uk> wrote in message
news:3ephc0pgs6 bq4rpfrmphsi0ib ck95factj@4ax.c om...[color=blue]
> Hi
>
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
>
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.[/color]

My understanding is with SQL 6.0 and I think 7.0 this would make a
difference (as SQL could have multiple file pointers).

This is no longer the case with SQL 2000.

Also, RAID 0 is a disaster waiting to happen.

Remember, if you have a 2 disk RAID 0 set, you've just doubled your chances
of a catastrophic failure.

If you have a 3 disk set, it's 3x, etc.

[color=blue]
>
> I am mainly interested in read performance rather than write.[/color]

Well, first move to a "real" RAID solution, 1, 10, 5 or the like.

Separate out files (say OS, data, logs) to separate PHYSICAL arrays (doesn't
help to repartition your existing array into 3 logical disks... you don't
get any more physical heads.)

And make sure you have RAM. Lots of it.[color=blue]
>
> I am just starting out with MS SQL
>
> Thanks for any info
>
>
>[/color]

[John Bell]

Re: Performance Question

Hi

To add to Gregs advice you may want to get yourself the following book:

http://www.microsoft.com/mspress/books/4944.asp

and this article and other:

http://www.sql-server-performance.com/rc_hardware_planning.asp

This may also help

http://www.acnc.com/04_01_10.html

John

<rc@die@you@!sp ammers.sandworm .demon.co.uk> wrote in message
news:3ephc0pgs6 bq4rpfrmphsi0ib ck95factj@4ax.c om...[color=blue]
> Hi
>
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
>
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.
>
> I am mainly interested in read performance rather than write.
>
> I am just starting out with MS SQL
>
> Thanks for any info
>
>
>[/color]

[rc]

Re: Performance Question

Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
<jbellnewsposts @hotmail.com> wrote:
[color=blue]
>Hi
>
>To add to Gregs advice you may want to get yourself the following book:
>http://www.microsoft.com/mspress/books/4944.asp
>
>and this article and other:
>http://www.sql-server-performance.co...e_planning.asp
>
>This may also help
>http://www.acnc.com/04_01_10.html
>
>John
>
><rc@die@you@!s pammers.sandwor m.demon.co.uk> wrote in message
>news:3ephc0pgs 6bq4rpfrmphsi0i bck95factj@4ax. com...[color=green]
>> Hi
>>
>> We have a SQL server on Win2k. the physical size of the db is about
>> 40G and the main table has approx 65m rows in it. At the moment the
>> entire database is on one data file. The entire server including the
>> OS is on a RAID 0 array with one RAID controller.
>>
>> My question is would I get any performance benefit if I was to have
>> more than several data file rather than one big data file, bearing in
>> mind that there is only one disk controller.
>>
>> I am mainly interested in read performance rather than write.
>>
>> I am just starting out with MS SQL
>>
>> Thanks for any info
>>
>>
>>[/color]
>[/color]

[John Bell]

Re: Performance Question

Hi

If the controller has multiple channels it will help if you configure a
different sub-systems for OS and logs using new disks. These will share
resources on the controller so if you can add memory to it that will also
help. Adding extra disks to the current array will help spread the disk
usage more, but may not show as much gain as having a new sub-system there
is an example in "Microsoft® SQL Server 2000T Performance Tuning Technical
Reference" that shows how adding a disk to an existing array can help.

John

Using multiple filegroups in their own files will help backup and
organisation.
"rc" <rc@spam.com> wrote in message
news:i3pic0pca4 dg6ga3cj4e1vr3a s98e558uu@4ax.c om...[color=blue]
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
> On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
> <jbellnewsposts @hotmail.com> wrote:
>[color=green]
> >Hi
> >
> >To add to Gregs advice you may want to get yourself the following book:
> >http://www.microsoft.com/mspress/books/4944.asp
> >
> >and this article and other:
> >http://www.sql-server-performance.co...e_planning.asp
> >
> >This may also help
> >http://www.acnc.com/04_01_10.html
> >
> >John
> >
> ><rc@die@you@!s pammers.sandwor m.demon.co.uk> wrote in message
> >news:3ephc0pgs 6bq4rpfrmphsi0i bck95factj@4ax. com...[color=darkred]
> >> Hi
> >>
> >> We have a SQL server on Win2k. the physical size of the db is about
> >> 40G and the main table has approx 65m rows in it. At the moment the
> >> entire database is on one data file. The entire server including the
> >> OS is on a RAID 0 array with one RAID controller.
> >>
> >> My question is would I get any performance benefit if I was to have
> >> more than several data file rather than one big data file, bearing in
> >> mind that there is only one disk controller.
> >>
> >> I am mainly interested in read performance rather than write.
> >>
> >> I am just starting out with MS SQL
> >>
> >> Thanks for any info
> >>
> >>
> >>[/color]
> >[/color]
>[/color]

[Greg D. Moore \(Strider\)]

Re: Performance Question


"rc" <rc@spam.com> wrote in message
news:i3pic0pca4 dg6ga3cj4e1vr3a s98e558uu@4ax.c om...[color=blue]
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored[/color]

Ok, that's good. :-)