Resource Isolation of Multiple SQL Instances

Hello,
I would like to know (without experimentation) if by using two instances of
SQL Server I can isolate a first database which requires high repeatability
in query update times from another much larger second database which may
consume both more CPU and memory resources.
Assume: (let me know also if this cannot be done)
(a) that the server has dual processors each SQL instance is assigned to its
own CPU.
(b) that I have allocated separate memory to each instance.
(I have read that the overall performance for a single instance is better
than multiple instances on the same server. This fact may be irrelavent if
I can live with a degraded overall performance but guarantee that the
instances are adequately isolated and queries remain repeatable on the first
database.)
Thanks in advance,
Ted HannahTed,
I think you've got the hang of it.
One thing to consider is that the DBMS relies on the OS. So, when the DBMS
has a lot to do, then the OS will place a heavier load as well. And, in your
scenario, you only have one OS. This means that your "OLTP" database will
still be somewhat affected by your "reporting" database (I'm guessing where
regarding OLTP and reporting). I don't think I've ever seen any tests on how
much this "bleeding" one can expect, but I'd thought I at least mention it.
My guess is that it won't be that much - main resource usage would be inside
each SQL Server.
--
Tibor Karaszi
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I would like to know (without experimentation) if by using two instances
of
> SQL Server I can isolate a first database which requires high
repeatability
> in query update times from another much larger second database which may
> consume both more CPU and memory resources.
> Assume: (let me know also if this cannot be done)
> (a) that the server has dual processors each SQL instance is assigned to
its
> own CPU.
> (b) that I have allocated separate memory to each instance.
> (I have read that the overall performance for a single instance is better
> than multiple instances on the same server. This fact may be irrelavent
if
> I can live with a degraded overall performance but guarantee that the
> instances are adequately isolated and queries remain repeatable on the
first
> database.)
>
> Thanks in advance,
> Ted Hannah
>|||Thanks - I would be very interested if anyone has measured this with some
real test. It would save some time. Speculation, unfortunately, won't be
good enough.
TED
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> Ted,
> I think you've got the hang of it.
> One thing to consider is that the DBMS relies on the OS. So, when the DBMS
> has a lot to do, then the OS will place a heavier load as well. And, in
your
> scenario, you only have one OS. This means that your "OLTP" database will
> still be somewhat affected by your "reporting" database (I'm guessing
where
> regarding OLTP and reporting). I don't think I've ever seen any tests on
how
> much this "bleeding" one can expect, but I'd thought I at least mention
it.
> My guess is that it won't be that much - main resource usage would be
inside
> each SQL Server.
> --
> Tibor Karaszi
>
> "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I would like to know (without experimentation) if by using two instances
> of
> > SQL Server I can isolate a first database which requires high
> repeatability
> > in query update times from another much larger second database which may
> > consume both more CPU and memory resources.
> > Assume: (let me know also if this cannot be done)
> > (a) that the server has dual processors each SQL instance is assigned to
> its
> > own CPU.
> > (b) that I have allocated separate memory to each instance.
> >
> > (I have read that the overall performance for a single instance is
better
> > than multiple instances on the same server. This fact may be irrelavent
> if
> > I can live with a degraded overall performance but guarantee that the
> > instances are adequately isolated and queries remain repeatable on the
> first
> > database.)
> >
> >
> > Thanks in advance,
> > Ted Hannah
> >
> >
>|||IO contention will continue to be a problem unless the databases are on
different PCI Busses, Controllers,and disks.
There are not any internal OS bottlenecks that I have seen that would
prevent you from successfully isolating workloads like this, but in a large
hetergeneous environment, it makes sense to keep the DSS and OLAP stuff on
different machines than OLTP. If you're looking at one server for two apps
and can seperate the IO channels, memory, and CPU affinity, then I think you
won't have any issues.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Ted Hannah" <tedhannah@.hotmail.com> wrote in message
news:u6IQbu#oDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Thanks - I would be very interested if anyone has measured this with some
> real test. It would save some time. Speculation, unfortunately, won't be
> good enough.
> TED
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:_22qb.32395$mU6.93437@.newsb.telia.net...
> > Ted,
> >
> > I think you've got the hang of it.
> > One thing to consider is that the DBMS relies on the OS. So, when the
DBMS
> > has a lot to do, then the OS will place a heavier load as well. And, in
> your
> > scenario, you only have one OS. This means that your "OLTP" database
will
> > still be somewhat affected by your "reporting" database (I'm guessing
> where
> > regarding OLTP and reporting). I don't think I've ever seen any tests on
> how
> > much this "bleeding" one can expect, but I'd thought I at least mention
> it.
> > My guess is that it won't be that much - main resource usage would be
> inside
> > each SQL Server.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Ted Hannah" <tedhannah@.hotmail.com> wrote in message
> > news:eGVF6eyoDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I would like to know (without experimentation) if by using two
instances
> > of
> > > SQL Server I can isolate a first database which requires high
> > repeatability
> > > in query update times from another much larger second database which
may
> > > consume both more CPU and memory resources.
> > > Assume: (let me know also if this cannot be done)
> > > (a) that the server has dual processors each SQL instance is assigned
to
> > its
> > > own CPU.
> > > (b) that I have allocated separate memory to each instance.
> > >
> > > (I have read that the overall performance for a single instance is
> better
> > > than multiple instances on the same server. This fact may be
irrelavent
> > if
> > > I can live with a degraded overall performance but guarantee that the
> > > instances are adequately isolated and queries remain repeatable on the
> > first
> > > database.)
> > >
> > >
> > > Thanks in advance,
> > > Ted Hannah
> > >
> > >
> >
> >
>