Hi

I'm building a data warehouse server using clustered SQL 2005(x64). We
have a known disk I/O performance bottleneck in our current SAN, but
my new servers have reasonable CPU and RAM. My question is, would I
benefit from multiple instances, giving multiple tempdb workspaces, or
will the use of the same underlying disk infrastructure negate any
benefits of splitting the activity.

There are some administrative benefits of multiple instances, in that
I can group my databases by customer, but the best performance would
be my top priority - until I can get a bigger, faster all new-and-
improved SAN.

Thanks.

If you have I/O issues with a single instance it won't get nay better (most
likely worse) with multiple instances unless you give the other instances
their own HBA's and set of disks. But you can do that with the single
instance as well. If you can't read or write data fast enough from the SAN
then SQL Server is not the place you need to fix, its the SAN. That is
assuming you have optimized your queries and such.

--
Andrew J. Kelly SQL MVP

"Steve" <elstevester@googlemail.com> wrote in message
news:1183045613.078140.111410@q75g2000hsh.googlegr oups.com...
> Hi
>
> I'm building a data warehouse server using clustered SQL 2005(x64). We
> have a known disk I/O performance bottleneck in our current SAN, but
> my new servers have reasonable CPU and RAM. My question is, would I
> benefit from multiple instances, giving multiple tempdb workspaces, or
> will the use of the same underlying disk infrastructure negate any
> benefits of splitting the activity.
>
> There are some administrative benefits of multiple instances, in that
> I can group my databases by customer, but the best performance would
> be my top priority - until I can get a bigger, faster all new-and-
> improved SAN.
>
> Thanks.
>

On 28 Jun, 22:10, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
wrote:
> If you have I/O issues with a single instance it won't get nay better (most
> likely worse) with multiple instances unless you give the other instances
> their own HBA's and set of disks. But you can do that with the single
> instance as well. If you can't read or write data fast enough from the SAN
> then SQL Server is not the place you need to fix, its the SAN. That is
> assuming you have optimized your queries and such.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Steve" <elsteves...@googlemail.com> wrote in message
>
> news:1183045613.078140.111410@q75g2000hsh.googlegr oups.com...
>
>
>
> > Hi
>
> > I'm building a data warehouse server using clustered SQL 2005(x64). We
> > have a known disk I/O performance bottleneck in our current SAN, but
> > my new servers have reasonable CPU and RAM. My question is, would I
> > benefit from multiple instances, giving multiple tempdb workspaces, or
> > will the use of the same underlying disk infrastructure negate any
> > benefits of splitting the activity.
>
> > There are some administrative benefits of multiple instances, in that
> > I can group my databases by customer, but the best performance would
> > be my top priority - until I can get a bigger, faster all new-and-
> > improved SAN.
>
> > Thanks.- Hide quoted text -
>
> - Show quoted text -

Thanks Andrew.

I agree in general - the SAN is due to be replaced in due course, and
more HBAs will be one of the priorities, but I have to build the
warehouse now. I was just wondering if there were any likely benefits,
eg, the multiple tempdbs, which the warehouse loading process hits
pretty hard.

Not really. Make sure that you have multiple files in Tempdb (1 per
processor with a minimum of 4) of equal size. If you have multiple drive
arrays you can possibly spread the files across the arrays. But if all the
arrays / channels are already overloaded there may not be too much you can
do file wise.

--
Andrew J. Kelly SQL MVP

"Steve" <elstevester@googlemail.com> wrote in message
news:1183109161.480059.49880@w5g2000hsg.googlegrou ps.com...
> On 28 Jun, 22:10, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
> wrote:
>> If you have I/O issues with a single instance it won't get nay better
>> (most
>> likely worse) with multiple instances unless you give the other instances
>> their own HBA's and set of disks. But you can do that with the single
>> instance as well. If you can't read or write data fast enough from the
>> SAN
>> then SQL Server is not the place you need to fix, its the SAN. That is
>> assuming you have optimized your queries and such.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve" <elsteves...@googlemail.com> wrote in message
>>
>> news:1183045613.078140.111410@q75g2000hsh.googlegr oups.com...
>>
>>
>>
>> > Hi
>>
>> > I'm building a data warehouse server using clustered SQL 2005(x64). We
>> > have a known disk I/O performance bottleneck in our current SAN, but
>> > my new servers have reasonable CPU and RAM. My question is, would I
>> > benefit from multiple instances, giving multiple tempdb workspaces, or
>> > will the use of the same underlying disk infrastructure negate any
>> > benefits of splitting the activity.
>>
>> > There are some administrative benefits of multiple instances, in that
>> > I can group my databases by customer, but the best performance would
>> > be my top priority - until I can get a bigger, faster all new-and-
>> > improved SAN.
>>
>> > Thanks.- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Andrew.
>
> I agree in general - the SAN is due to be replaced in due course, and
> more HBAs will be one of the priorities, but I have to build the
> warehouse now. I was just wondering if there were any likely benefits,
> eg, the multiple tempdbs, which the warehouse loading process hits
> pretty hard.
>