Hello All,
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,
Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Monday, March 19, 2012
multiple databases/single instance vs multiple instances
Hello All,
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,|||Hi,
I am in no way knowledgeable of SQL Server accept at the basic level,
however the question I need to ask may be relevant to the one posed and
answered here.
I have SQL Server 2005 with two large application databases, HP Openview
Service Desk and Centennial Discovery 2005. My question is, does anyone see a
problem with running both these databases on one server? I am being told that
it could be by the support people who provided us with Discovery 2005 as it
has been having "issues", although they cannot define the cause.
Cheers
Matt
"Joe Yong" wrote:
> Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> difference.
> Here are a few to consider:
> If you want to have granular control over resource allocation (memory,
> CPU,...) multi-instance is pretty much what you have to do. This is true for
> 2000 and 2005. For example, if you want to limit the amount of memory a
> database can consume, you can only do it at the instance level.
> Security isolation is another reason to consider multi-instance vs.
> single-instance/multi-db. If your users care a lot about not having anyone
> even know the names of their DB objects (meta data in general), then you're
> again looking at multi-instance. If you're on 2005, you're ok because of the
> security improvements (meta data security).
> If you're on the 32-bit platform and your apps have high demands on virtual
> memory, you may need to go multi-instance. Each 32-bit instance can address
> no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> stacks, etc...) depending on which edition you're on. If you're running
> 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> ceiling. Anything above that (AWE memory) is good for data caching not the
> other stuff. So if you are facing memory pressure in the VM space, multi
> instance gives each instance it's own 2, 3 or 4GB. This is true for both
> 2000 and 2005. If you're on 64-bit then it doesn't matter.
> There's also the sharing of model, tempdb and master. In some cases, the
> sharing is good (eg. only 1 master to backup) while others might not (eg.
> shared tempdb could result in contention).
> hth.
> Good luck.
>
> joe.
>
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > Hello All,
> >
> > Can someone please explain the con and pro of using single SQL Server
> > instance to store multiple databases vs using multiple SQL Server
> > instances
> > that store 1 database per instance? Both choices are on a single server
> > machine. Any significant performance difference?
> >
> > Thanks in Advance,
>
>|||SQL Server 2000 even not 2005.
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > difference.
> >
> > Here are a few to consider:
> >
> > If you want to have granular control over resource allocation (memory,
> > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > 2000 and 2005. For example, if you want to limit the amount of memory a
> > database can consume, you can only do it at the instance level.
> >
> > Security isolation is another reason to consider multi-instance vs.
> > single-instance/multi-db. If your users care a lot about not having anyone
> > even know the names of their DB objects (meta data in general), then you're
> > again looking at multi-instance. If you're on 2005, you're ok because of the
> > security improvements (meta data security).
> >
> > If you're on the 32-bit platform and your apps have high demands on virtual
> > memory, you may need to go multi-instance. Each 32-bit instance can address
> > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > stacks, etc...) depending on which edition you're on. If you're running
> > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > ceiling. Anything above that (AWE memory) is good for data caching not the
> > other stuff. So if you are facing memory pressure in the VM space, multi
> > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> >
> > There's also the sharing of model, tempdb and master. In some cases, the
> > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > shared tempdb could result in contention).
> >
> > hth.
> >
> > Good luck.
> >
> >
> > joe.
> >
> >
> >
> > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > Hello All,
> > >
> > > Can someone please explain the con and pro of using single SQL Server
> > > instance to store multiple databases vs using multiple SQL Server
> > > instances
> > > that store 1 database per instance? Both choices are on a single server
> > > machine. Any significant performance difference?
> > >
> > > Thanks in Advance,
> >
> >
> >|||As Matt,
It should not be a problem running multiple databases in a single instance.
What kind of issues do you meet? If it is performance-related, running the
perf monitor on SQL Server counters would reveal the possible bottleneck.
Good luck,
Franky
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > difference.
> >
> > Here are a few to consider:
> >
> > If you want to have granular control over resource allocation (memory,
> > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > 2000 and 2005. For example, if you want to limit the amount of memory a
> > database can consume, you can only do it at the instance level.
> >
> > Security isolation is another reason to consider multi-instance vs.
> > single-instance/multi-db. If your users care a lot about not having anyone
> > even know the names of their DB objects (meta data in general), then you're
> > again looking at multi-instance. If you're on 2005, you're ok because of the
> > security improvements (meta data security).
> >
> > If you're on the 32-bit platform and your apps have high demands on virtual
> > memory, you may need to go multi-instance. Each 32-bit instance can address
> > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > stacks, etc...) depending on which edition you're on. If you're running
> > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > ceiling. Anything above that (AWE memory) is good for data caching not the
> > other stuff. So if you are facing memory pressure in the VM space, multi
> > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> >
> > There's also the sharing of model, tempdb and master. In some cases, the
> > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > shared tempdb could result in contention).
> >
> > hth.
> >
> > Good luck.
> >
> >
> > joe.
> >
> >
> >
> > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > Hello All,
> > >
> > > Can someone please explain the con and pro of using single SQL Server
> > > instance to store multiple databases vs using multiple SQL Server
> > > instances
> > > that store 1 database per instance? Both choices are on a single server
> > > machine. Any significant performance difference?
> > >
> > > Thanks in Advance,
> >
> >
> >|||Hi Franky,
Well, prior to the problems occuring, performance was fine. When the problem
occured, the agent exectuable for the discovery service was maxing one of the
CPU threads to 100% and was doing little in the way of processing any
information (it was but very very very very slowly), also items in the
database were not showing in the front end application and device counts were
a mess, this did not affect HP OV SD however. We tried to use SQL Profiler to
isolate the problem, but had no idea what to look for, after a database
restore the problem seemed to go away, but then came back, i have since
reinstalled completely the discovery software and created a new database, but
i dont want this to happen again, our support for the product in question
suggested it was because both Discovery 2005 and HP OV SD are heavy users
(though our performance indicators show this not to be an issue, as since the
problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
time, its now around 1-12% depending on usage of service desk.
I am trying to basically answer their question can sql handle them both?
"Franky" wrote:
> As Matt,
> It should not be a problem running multiple databases in a single instance.
> What kind of issues do you meet? If it is performance-related, running the
> perf monitor on SQL Server counters would reveal the possible bottleneck.
> Good luck,
> Franky
> "Intridium" wrote:
> > Hi,
> >
> > I am in no way knowledgeable of SQL Server accept at the basic level,
> > however the question I need to ask may be relevant to the one posed and
> > answered here.
> >
> > I have SQL Server 2005 with two large application databases, HP Openview
> > Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> > problem with running both these databases on one server? I am being told that
> > it could be by the support people who provided us with Discovery 2005 as it
> > has been having "issues", although they cannot define the cause.
> >
> > Cheers
> >
> > Matt
> >
> > "Joe Yong" wrote:
> >
> > > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > > difference.
> > >
> > > Here are a few to consider:
> > >
> > > If you want to have granular control over resource allocation (memory,
> > > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > > 2000 and 2005. For example, if you want to limit the amount of memory a
> > > database can consume, you can only do it at the instance level.
> > >
> > > Security isolation is another reason to consider multi-instance vs.
> > > single-instance/multi-db. If your users care a lot about not having anyone
> > > even know the names of their DB objects (meta data in general), then you're
> > > again looking at multi-instance. If you're on 2005, you're ok because of the
> > > security improvements (meta data security).
> > >
> > > If you're on the 32-bit platform and your apps have high demands on virtual
> > > memory, you may need to go multi-instance. Each 32-bit instance can address
> > > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > > stacks, etc...) depending on which edition you're on. If you're running
> > > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > > ceiling. Anything above that (AWE memory) is good for data caching not the
> > > other stuff. So if you are facing memory pressure in the VM space, multi
> > > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> > >
> > > There's also the sharing of model, tempdb and master. In some cases, the
> > > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > > shared tempdb could result in contention).
> > >
> > > hth.
> > >
> > > Good luck.
> > >
> > >
> > > joe.
> > >
> > >
> > >
> > > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > > Hello All,
> > > >
> > > > Can someone please explain the con and pro of using single SQL Server
> > > > instance to store multiple databases vs using multiple SQL Server
> > > > instances
> > > > that store 1 database per instance? Both choices are on a single server
> > > > machine. Any significant performance difference?
> > > >
> > > > Thanks in Advance,
> > >
> > >
> > >|||Hi, Intridium,
Basically, sql server can host mutiple databases without problem, I have
managed server is heavily used and get 50 databases in one instance. it
works fine.the problem you met is in the application,
there are several way to cause the probem.
query large set of data, scan the table.
query large set of in a transaction. that would block the other process to
get resource.
may be run a query in a loop.
It is just 2c, I hope it is helpful.
Yifei
"Intridium" <Intridium@.discussions.microsoft.com> wrote in message
news:80FB8054-3EDE-4D4E-832A-C5AB5BA60108@.microsoft.com...
> Hi Franky,
> Well, prior to the problems occuring, performance was fine. When the
problem
> occured, the agent exectuable for the discovery service was maxing one of
the
> CPU threads to 100% and was doing little in the way of processing any
> information (it was but very very very very slowly), also items in the
> database were not showing in the front end application and device counts
were
> a mess, this did not affect HP OV SD however. We tried to use SQL Profiler
to
> isolate the problem, but had no idea what to look for, after a database
> restore the problem seemed to go away, but then came back, i have since
> reinstalled completely the discovery software and created a new database,
but
> i dont want this to happen again, our support for the product in question
> suggested it was because both Discovery 2005 and HP OV SD are heavy users
> (though our performance indicators show this not to be an issue, as since
the
> problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
> time, its now around 1-12% depending on usage of service desk.
> I am trying to basically answer their question can sql handle them both?
>
> "Franky" wrote:
> > As Matt,
> >
> > It should not be a problem running multiple databases in a single
instance.
> > What kind of issues do you meet? If it is performance-related, running
the
> > perf monitor on SQL Server counters would reveal the possible
bottleneck.
> >
> > Good luck,
> >
> > Franky
> >
> > "Intridium" wrote:
> >
> > > Hi,
> > >
> > > I am in no way knowledgeable of SQL Server accept at the basic level,
> > > however the question I need to ask may be relevant to the one posed
and
> > > answered here.
> > >
> > > I have SQL Server 2005 with two large application databases, HP
Openview
> > > Service Desk and Centennial Discovery 2005. My question is, does
anyone see a
> > > problem with running both these databases on one server? I am being
told that
> > > it could be by the support people who provided us with Discovery 2005
as it
> > > has been having "issues", although they cannot define the cause.
> > >
> > > Cheers
> > >
> > > Matt
> > >
> > > "Joe Yong" wrote:
> > >
> > > > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit
makes a
> > > > difference.
> > > >
> > > > Here are a few to consider:
> > > >
> > > > If you want to have granular control over resource allocation
(memory,
> > > > CPU,...) multi-instance is pretty much what you have to do. This is
true for
> > > > 2000 and 2005. For example, if you want to limit the amount of
memory a
> > > > database can consume, you can only do it at the instance level.
> > > >
> > > > Security isolation is another reason to consider multi-instance vs.
> > > > single-instance/multi-db. If your users care a lot about not having
anyone
> > > > even know the names of their DB objects (meta data in general), then
you're
> > > > again looking at multi-instance. If you're on 2005, you're ok
because of the
> > > > security improvements (meta data security).
> > > >
> > > > If you're on the 32-bit platform and your apps have high demands on
virtual
> > > > memory, you may need to go multi-instance. Each 32-bit instance can
address
> > > > no more than 2 or 3 GB of virtual memory (used for proc cache,
locks, thread
> > > > stacks, etc...) depending on which edition you're on. If you're
running
> > > > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > > > ceiling. Anything above that (AWE memory) is good for data caching
not the
> > > > other stuff. So if you are facing memory pressure in the VM space,
multi
> > > > instance gives each instance it's own 2, 3 or 4GB. This is true for
both
> > > > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> > > >
> > > > There's also the sharing of model, tempdb and master. In some cases,
the
> > > > sharing is good (eg. only 1 master to backup) while others might not
(eg.
> > > > shared tempdb could result in contention).
> > > >
> > > > hth.
> > > >
> > > > Good luck.
> > > >
> > > >
> > > > joe.
> > > >
> > > >
> > > >
> > > > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > > > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > > > Hello All,
> > > > >
> > > > > Can someone please explain the con and pro of using single SQL
Server
> > > > > instance to store multiple databases vs using multiple SQL Server
> > > > > instances
> > > > > that store 1 database per instance? Both choices are on a single
server
> > > > > machine. Any significant performance difference?
> > > > >
> > > > > Thanks in Advance,
> > > >
> > > >
> > > >|||Hi there,
in theory (and often in practice too!) you can have multiple databases
on a single SQL Server.
The problem you are seeing here is down to the fact that the Centennial
server (the box with the Centennial Server Agent) formulates it's
requests and then passes them into the Discovery database via stored
procedures.
The Centennial stored procedures do almost all of the work and this can
cause 100% CPU utilisation.
As it happens, Centennial isn't doing anything wrong. 5 years ago we
were in the client-server world where a client would retrieve data from
the database (SQL, Oracle, DB2, etc) do lots of processing and then
write the results back.
In this brave new world, though, we expect to pass simple transactions
"add this software to this asset" to the database and have it perform
all of the calculations and updates required to write this to the
database.
The problem you are experiencing is that you presumably have a large
number of "traditional" client/server type apps writing to databases on
your server and this isn't compatible with the heavy stored procedure /
CPU utilisation that Centennial exhibits.
Centennials recommendation (and one that I support) is to have a
dedicated database server for their application.
As a workaround, ensure that your Centennial audits are scheduled
during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
as this is where the bulk of the stored procedure calls are made.
Cheers,
Skot|||Hi,
My question is I'm trying to install multiple instances but I can't uncheck
the default selection because it's grayed out. I've selected the upgrade
selection in the previous window but it will not allow me to change the name.
Does anyone know how I can get pass this issue to install another instance
of SQL? My goal is to install Project Server 2003 which uses SQL.
I'm running SQL Server 2000 SP4 on a Windows 2003 server.
Any suggestions would be helpful.
Thanks,
Lewis
"scott.leckie@.btclick.com" wrote:
> Hi there,
> in theory (and often in practice too!) you can have multiple databases
> on a single SQL Server.
> The problem you are seeing here is down to the fact that the Centennial
> server (the box with the Centennial Server Agent) formulates it's
> requests and then passes them into the Discovery database via stored
> procedures.
> The Centennial stored procedures do almost all of the work and this can
> cause 100% CPU utilisation.
> As it happens, Centennial isn't doing anything wrong. 5 years ago we
> were in the client-server world where a client would retrieve data from
> the database (SQL, Oracle, DB2, etc) do lots of processing and then
> write the results back.
> In this brave new world, though, we expect to pass simple transactions
> "add this software to this asset" to the database and have it perform
> all of the calculations and updates required to write this to the
> database.
> The problem you are experiencing is that you presumably have a large
> number of "traditional" client/server type apps writing to databases on
> your server and this isn't compatible with the heavy stored procedure /
> CPU utilisation that Centennial exhibits.
> Centennials recommendation (and one that I support) is to have a
> dedicated database server for their application.
> As a workaround, ensure that your Centennial audits are scheduled
> during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
> as this is where the bulk of the stored procedure calls are made.
> Cheers,
> Skot
>
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,|||Hi,
I am in no way knowledgeable of SQL Server accept at the basic level,
however the question I need to ask may be relevant to the one posed and
answered here.
I have SQL Server 2005 with two large application databases, HP Openview
Service Desk and Centennial Discovery 2005. My question is, does anyone see a
problem with running both these databases on one server? I am being told that
it could be by the support people who provided us with Discovery 2005 as it
has been having "issues", although they cannot define the cause.
Cheers
Matt
"Joe Yong" wrote:
> Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> difference.
> Here are a few to consider:
> If you want to have granular control over resource allocation (memory,
> CPU,...) multi-instance is pretty much what you have to do. This is true for
> 2000 and 2005. For example, if you want to limit the amount of memory a
> database can consume, you can only do it at the instance level.
> Security isolation is another reason to consider multi-instance vs.
> single-instance/multi-db. If your users care a lot about not having anyone
> even know the names of their DB objects (meta data in general), then you're
> again looking at multi-instance. If you're on 2005, you're ok because of the
> security improvements (meta data security).
> If you're on the 32-bit platform and your apps have high demands on virtual
> memory, you may need to go multi-instance. Each 32-bit instance can address
> no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> stacks, etc...) depending on which edition you're on. If you're running
> 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> ceiling. Anything above that (AWE memory) is good for data caching not the
> other stuff. So if you are facing memory pressure in the VM space, multi
> instance gives each instance it's own 2, 3 or 4GB. This is true for both
> 2000 and 2005. If you're on 64-bit then it doesn't matter.
> There's also the sharing of model, tempdb and master. In some cases, the
> sharing is good (eg. only 1 master to backup) while others might not (eg.
> shared tempdb could result in contention).
> hth.
> Good luck.
>
> joe.
>
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > Hello All,
> >
> > Can someone please explain the con and pro of using single SQL Server
> > instance to store multiple databases vs using multiple SQL Server
> > instances
> > that store 1 database per instance? Both choices are on a single server
> > machine. Any significant performance difference?
> >
> > Thanks in Advance,
>
>|||SQL Server 2000 even not 2005.
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > difference.
> >
> > Here are a few to consider:
> >
> > If you want to have granular control over resource allocation (memory,
> > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > 2000 and 2005. For example, if you want to limit the amount of memory a
> > database can consume, you can only do it at the instance level.
> >
> > Security isolation is another reason to consider multi-instance vs.
> > single-instance/multi-db. If your users care a lot about not having anyone
> > even know the names of their DB objects (meta data in general), then you're
> > again looking at multi-instance. If you're on 2005, you're ok because of the
> > security improvements (meta data security).
> >
> > If you're on the 32-bit platform and your apps have high demands on virtual
> > memory, you may need to go multi-instance. Each 32-bit instance can address
> > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > stacks, etc...) depending on which edition you're on. If you're running
> > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > ceiling. Anything above that (AWE memory) is good for data caching not the
> > other stuff. So if you are facing memory pressure in the VM space, multi
> > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> >
> > There's also the sharing of model, tempdb and master. In some cases, the
> > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > shared tempdb could result in contention).
> >
> > hth.
> >
> > Good luck.
> >
> >
> > joe.
> >
> >
> >
> > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > Hello All,
> > >
> > > Can someone please explain the con and pro of using single SQL Server
> > > instance to store multiple databases vs using multiple SQL Server
> > > instances
> > > that store 1 database per instance? Both choices are on a single server
> > > machine. Any significant performance difference?
> > >
> > > Thanks in Advance,
> >
> >
> >|||As Matt,
It should not be a problem running multiple databases in a single instance.
What kind of issues do you meet? If it is performance-related, running the
perf monitor on SQL Server counters would reveal the possible bottleneck.
Good luck,
Franky
"Intridium" wrote:
> Hi,
> I am in no way knowledgeable of SQL Server accept at the basic level,
> however the question I need to ask may be relevant to the one posed and
> answered here.
> I have SQL Server 2005 with two large application databases, HP Openview
> Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> problem with running both these databases on one server? I am being told that
> it could be by the support people who provided us with Discovery 2005 as it
> has been having "issues", although they cannot define the cause.
> Cheers
> Matt
> "Joe Yong" wrote:
> > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > difference.
> >
> > Here are a few to consider:
> >
> > If you want to have granular control over resource allocation (memory,
> > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > 2000 and 2005. For example, if you want to limit the amount of memory a
> > database can consume, you can only do it at the instance level.
> >
> > Security isolation is another reason to consider multi-instance vs.
> > single-instance/multi-db. If your users care a lot about not having anyone
> > even know the names of their DB objects (meta data in general), then you're
> > again looking at multi-instance. If you're on 2005, you're ok because of the
> > security improvements (meta data security).
> >
> > If you're on the 32-bit platform and your apps have high demands on virtual
> > memory, you may need to go multi-instance. Each 32-bit instance can address
> > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > stacks, etc...) depending on which edition you're on. If you're running
> > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > ceiling. Anything above that (AWE memory) is good for data caching not the
> > other stuff. So if you are facing memory pressure in the VM space, multi
> > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> >
> > There's also the sharing of model, tempdb and master. In some cases, the
> > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > shared tempdb could result in contention).
> >
> > hth.
> >
> > Good luck.
> >
> >
> > joe.
> >
> >
> >
> > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > Hello All,
> > >
> > > Can someone please explain the con and pro of using single SQL Server
> > > instance to store multiple databases vs using multiple SQL Server
> > > instances
> > > that store 1 database per instance? Both choices are on a single server
> > > machine. Any significant performance difference?
> > >
> > > Thanks in Advance,
> >
> >
> >|||Hi Franky,
Well, prior to the problems occuring, performance was fine. When the problem
occured, the agent exectuable for the discovery service was maxing one of the
CPU threads to 100% and was doing little in the way of processing any
information (it was but very very very very slowly), also items in the
database were not showing in the front end application and device counts were
a mess, this did not affect HP OV SD however. We tried to use SQL Profiler to
isolate the problem, but had no idea what to look for, after a database
restore the problem seemed to go away, but then came back, i have since
reinstalled completely the discovery software and created a new database, but
i dont want this to happen again, our support for the product in question
suggested it was because both Discovery 2005 and HP OV SD are heavy users
(though our performance indicators show this not to be an issue, as since the
problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
time, its now around 1-12% depending on usage of service desk.
I am trying to basically answer their question can sql handle them both?
"Franky" wrote:
> As Matt,
> It should not be a problem running multiple databases in a single instance.
> What kind of issues do you meet? If it is performance-related, running the
> perf monitor on SQL Server counters would reveal the possible bottleneck.
> Good luck,
> Franky
> "Intridium" wrote:
> > Hi,
> >
> > I am in no way knowledgeable of SQL Server accept at the basic level,
> > however the question I need to ask may be relevant to the one posed and
> > answered here.
> >
> > I have SQL Server 2005 with two large application databases, HP Openview
> > Service Desk and Centennial Discovery 2005. My question is, does anyone see a
> > problem with running both these databases on one server? I am being told that
> > it could be by the support people who provided us with Discovery 2005 as it
> > has been having "issues", although they cannot define the cause.
> >
> > Cheers
> >
> > Matt
> >
> > "Joe Yong" wrote:
> >
> > > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
> > > difference.
> > >
> > > Here are a few to consider:
> > >
> > > If you want to have granular control over resource allocation (memory,
> > > CPU,...) multi-instance is pretty much what you have to do. This is true for
> > > 2000 and 2005. For example, if you want to limit the amount of memory a
> > > database can consume, you can only do it at the instance level.
> > >
> > > Security isolation is another reason to consider multi-instance vs.
> > > single-instance/multi-db. If your users care a lot about not having anyone
> > > even know the names of their DB objects (meta data in general), then you're
> > > again looking at multi-instance. If you're on 2005, you're ok because of the
> > > security improvements (meta data security).
> > >
> > > If you're on the 32-bit platform and your apps have high demands on virtual
> > > memory, you may need to go multi-instance. Each 32-bit instance can address
> > > no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
> > > stacks, etc...) depending on which edition you're on. If you're running
> > > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > > ceiling. Anything above that (AWE memory) is good for data caching not the
> > > other stuff. So if you are facing memory pressure in the VM space, multi
> > > instance gives each instance it's own 2, 3 or 4GB. This is true for both
> > > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> > >
> > > There's also the sharing of model, tempdb and master. In some cases, the
> > > sharing is good (eg. only 1 master to backup) while others might not (eg.
> > > shared tempdb could result in contention).
> > >
> > > hth.
> > >
> > > Good luck.
> > >
> > >
> > > joe.
> > >
> > >
> > >
> > > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > > Hello All,
> > > >
> > > > Can someone please explain the con and pro of using single SQL Server
> > > > instance to store multiple databases vs using multiple SQL Server
> > > > instances
> > > > that store 1 database per instance? Both choices are on a single server
> > > > machine. Any significant performance difference?
> > > >
> > > > Thanks in Advance,
> > >
> > >
> > >|||Hi, Intridium,
Basically, sql server can host mutiple databases without problem, I have
managed server is heavily used and get 50 databases in one instance. it
works fine.the problem you met is in the application,
there are several way to cause the probem.
query large set of data, scan the table.
query large set of in a transaction. that would block the other process to
get resource.
may be run a query in a loop.
It is just 2c, I hope it is helpful.
Yifei
"Intridium" <Intridium@.discussions.microsoft.com> wrote in message
news:80FB8054-3EDE-4D4E-832A-C5AB5BA60108@.microsoft.com...
> Hi Franky,
> Well, prior to the problems occuring, performance was fine. When the
problem
> occured, the agent exectuable for the discovery service was maxing one of
the
> CPU threads to 100% and was doing little in the way of processing any
> information (it was but very very very very slowly), also items in the
> database were not showing in the front end application and device counts
were
> a mess, this did not affect HP OV SD however. We tried to use SQL Profiler
to
> isolate the problem, but had no idea what to look for, after a database
> restore the problem seemed to go away, but then came back, i have since
> reinstalled completely the discovery software and created a new database,
but
> i dont want this to happen again, our support for the product in question
> suggested it was because both Discovery 2005 and HP OV SD are heavy users
> (though our performance indicators show this not to be an issue, as since
the
> problem has gone we dont have Sql_server.exe using a constant 25%-56% cpu
> time, its now around 1-12% depending on usage of service desk.
> I am trying to basically answer their question can sql handle them both?
>
> "Franky" wrote:
> > As Matt,
> >
> > It should not be a problem running multiple databases in a single
instance.
> > What kind of issues do you meet? If it is performance-related, running
the
> > perf monitor on SQL Server counters would reveal the possible
bottleneck.
> >
> > Good luck,
> >
> > Franky
> >
> > "Intridium" wrote:
> >
> > > Hi,
> > >
> > > I am in no way knowledgeable of SQL Server accept at the basic level,
> > > however the question I need to ask may be relevant to the one posed
and
> > > answered here.
> > >
> > > I have SQL Server 2005 with two large application databases, HP
Openview
> > > Service Desk and Centennial Discovery 2005. My question is, does
anyone see a
> > > problem with running both these databases on one server? I am being
told that
> > > it could be by the support people who provided us with Discovery 2005
as it
> > > has been having "issues", although they cannot define the cause.
> > >
> > > Cheers
> > >
> > > Matt
> > >
> > > "Joe Yong" wrote:
> > >
> > > > Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit
makes a
> > > > difference.
> > > >
> > > > Here are a few to consider:
> > > >
> > > > If you want to have granular control over resource allocation
(memory,
> > > > CPU,...) multi-instance is pretty much what you have to do. This is
true for
> > > > 2000 and 2005. For example, if you want to limit the amount of
memory a
> > > > database can consume, you can only do it at the instance level.
> > > >
> > > > Security isolation is another reason to consider multi-instance vs.
> > > > single-instance/multi-db. If your users care a lot about not having
anyone
> > > > even know the names of their DB objects (meta data in general), then
you're
> > > > again looking at multi-instance. If you're on 2005, you're ok
because of the
> > > > security improvements (meta data security).
> > > >
> > > > If you're on the 32-bit platform and your apps have high demands on
virtual
> > > > memory, you may need to go multi-instance. Each 32-bit instance can
address
> > > > no more than 2 or 3 GB of virtual memory (used for proc cache,
locks, thread
> > > > stacks, etc...) depending on which edition you're on. If you're
running
> > > > 32-bit SQL Server on an x64 server, you get up to 4GB but that's the
> > > > ceiling. Anything above that (AWE memory) is good for data caching
not the
> > > > other stuff. So if you are facing memory pressure in the VM space,
multi
> > > > instance gives each instance it's own 2, 3 or 4GB. This is true for
both
> > > > 2000 and 2005. If you're on 64-bit then it doesn't matter.
> > > >
> > > > There's also the sharing of model, tempdb and master. In some cases,
the
> > > > sharing is good (eg. only 1 master to backup) while others might not
(eg.
> > > > shared tempdb could result in contention).
> > > >
> > > > hth.
> > > >
> > > > Good luck.
> > > >
> > > >
> > > > joe.
> > > >
> > > >
> > > >
> > > > "Franky" <Franky@.discussions.microsoft.com> wrote in message
> > > > news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> > > > > Hello All,
> > > > >
> > > > > Can someone please explain the con and pro of using single SQL
Server
> > > > > instance to store multiple databases vs using multiple SQL Server
> > > > > instances
> > > > > that store 1 database per instance? Both choices are on a single
server
> > > > > machine. Any significant performance difference?
> > > > >
> > > > > Thanks in Advance,
> > > >
> > > >
> > > >|||Hi there,
in theory (and often in practice too!) you can have multiple databases
on a single SQL Server.
The problem you are seeing here is down to the fact that the Centennial
server (the box with the Centennial Server Agent) formulates it's
requests and then passes them into the Discovery database via stored
procedures.
The Centennial stored procedures do almost all of the work and this can
cause 100% CPU utilisation.
As it happens, Centennial isn't doing anything wrong. 5 years ago we
were in the client-server world where a client would retrieve data from
the database (SQL, Oracle, DB2, etc) do lots of processing and then
write the results back.
In this brave new world, though, we expect to pass simple transactions
"add this software to this asset" to the database and have it perform
all of the calculations and updates required to write this to the
database.
The problem you are experiencing is that you presumably have a large
number of "traditional" client/server type apps writing to databases on
your server and this isn't compatible with the heavy stored procedure /
CPU utilisation that Centennial exhibits.
Centennials recommendation (and one that I support) is to have a
dedicated database server for their application.
As a workaround, ensure that your Centennial audits are scheduled
during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
as this is where the bulk of the stored procedure calls are made.
Cheers,
Skot|||Hi,
My question is I'm trying to install multiple instances but I can't uncheck
the default selection because it's grayed out. I've selected the upgrade
selection in the previous window but it will not allow me to change the name.
Does anyone know how I can get pass this issue to install another instance
of SQL? My goal is to install Project Server 2003 which uses SQL.
I'm running SQL Server 2000 SP4 on a Windows 2003 server.
Any suggestions would be helpful.
Thanks,
Lewis
"scott.leckie@.btclick.com" wrote:
> Hi there,
> in theory (and often in practice too!) you can have multiple databases
> on a single SQL Server.
> The problem you are seeing here is down to the fact that the Centennial
> server (the box with the Centennial Server Agent) formulates it's
> requests and then passes them into the Discovery database via stored
> procedures.
> The Centennial stored procedures do almost all of the work and this can
> cause 100% CPU utilisation.
> As it happens, Centennial isn't doing anything wrong. 5 years ago we
> were in the client-server world where a client would retrieve data from
> the database (SQL, Oracle, DB2, etc) do lots of processing and then
> write the results back.
> In this brave new world, though, we expect to pass simple transactions
> "add this software to this asset" to the database and have it perform
> all of the calculations and updates required to write this to the
> database.
> The problem you are experiencing is that you presumably have a large
> number of "traditional" client/server type apps writing to databases on
> your server and this isn't compatible with the heavy stored procedure /
> CPU utilisation that Centennial exhibits.
> Centennials recommendation (and one that I support) is to have a
> dedicated database server for their application.
> As a workaround, ensure that your Centennial audits are scheduled
> during a quiet period (e.g. 1900-0500 overnight, or over the weekend)
> as this is where the bulk of the stored procedure calls are made.
> Cheers,
> Skot
>
multiple databases/single instance vs multiple instances
Hello All,
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,
Can someone please explain the con and pro of using single SQL Server
instance to store multiple databases vs using multiple SQL Server instances
that store 1 database per instance? Both choices are on a single server
machine. Any significant performance difference?
Thanks in Advance,Depends if you're on SQL Server 2000 or 2005. Also, 32 or 64 bit makes a
difference.
Here are a few to consider:
If you want to have granular control over resource allocation (memory,
CPU,...) multi-instance is pretty much what you have to do. This is true for
2000 and 2005. For example, if you want to limit the amount of memory a
database can consume, you can only do it at the instance level.
Security isolation is another reason to consider multi-instance vs.
single-instance/multi-db. If your users care a lot about not having anyone
even know the names of their DB objects (meta data in general), then you're
again looking at multi-instance. If you're on 2005, you're ok because of the
security improvements (meta data security).
If you're on the 32-bit platform and your apps have high demands on virtual
memory, you may need to go multi-instance. Each 32-bit instance can address
no more than 2 or 3 GB of virtual memory (used for proc cache, locks, thread
stacks, etc...) depending on which edition you're on. If you're running
32-bit SQL Server on an x64 server, you get up to 4GB but that's the
ceiling. Anything above that (AWE memory) is good for data caching not the
other stuff. So if you are facing memory pressure in the VM space, multi
instance gives each instance it's own 2, 3 or 4GB. This is true for both
2000 and 2005. If you're on 64-bit then it doesn't matter.
There's also the sharing of model, tempdb and master. In some cases, the
sharing is good (eg. only 1 master to backup) while others might not (eg.
shared tempdb could result in contention).
hth.
Good luck.
joe.
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:716390E9-0216-46ED-A188-64DAFA87BD61@.microsoft.com...
> Hello All,
> Can someone please explain the con and pro of using single SQL Server
> instance to store multiple databases vs using multiple SQL Server
> instances
> that store 1 database per instance? Both choices are on a single server
> machine. Any significant performance difference?
> Thanks in Advance,
Friday, March 9, 2012
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They ar
e
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They ar
e
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
Thanks,
Dan Jurden
http://www.eps-cs.com
Dan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
Thanks,
Dan Jurden
http://www.eps-cs.com
Dan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Multiple Customers Same Database
We have a client that has a web application that is accessed by multiple
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
customers. They are using one database to store all data for all customers.
One potential customer is concerned about storing the data this way. They are
worried that their data could be accessed by other customers.
Our client has asked us to come up with a list of pros and cons of storing
all customers data in the same database. We have brainstormed and came up
with a few. Can any of you offer your thoughts?
--
Thanks,
Dan Jurden
http://www.eps-cs.comDan,
If you have a large number of customers, then maintaining those databases
via Enterprise Manager can be challenging. You also have to deal with
upgrades, such as a schema change across all those databases, as well as the
fact that you'll have to duplicate stored procedures (most likely) across
all the dbs.
On the other hand, for a relatively small number of customers, say less than
20 or so, having them all in separate databases is much much cleaner.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Dan Jurden" <DanJurden@.discussions.microsoft.com> wrote in message
news:1FFE95EE-645E-4B01-A5FC-D351F1B92467@.microsoft.com...
> We have a client that has a web application that is accessed by multiple
> customers. They are using one database to store all data for all
> customers.
> One potential customer is concerned about storing the data this way. They
> are
> worried that their data could be accessed by other customers.
> Our client has asked us to come up with a list of pros and cons of storing
> all customers data in the same database. We have brainstormed and came up
> with a few. Can any of you offer your thoughts?
> --
> Thanks,
> Dan Jurden
> http://www.eps-cs.com
Saturday, February 25, 2012
multiple calls to SP
Hi group,
I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))
All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.
The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.
When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.
Can someone give me some pointers on where to look for performance wins?
If you like I can add the SP's
Regards,
Sander
Call it one time instead of 180 times =;o)
But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.
Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?
/Kenneth
|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.
I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.
Do you know what possibilities I've got for tuning the 180 calls?
|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)
This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType
IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END
SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)
SET DATEFIRST 1
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)
BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval
This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)
--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType
SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)
SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)
BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval
|||
SDerix wrote:
Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.Do you know what possibilities I've got for tuning the 180 calls?
Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)
On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.
It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.
Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?
Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?
spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)
In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....
So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?
/Kenneth
|||I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.
Did you try creating indexes on the global temp tables?
Subscribe to:
Posts (Atom)