A customer asked me at a recent TechNet Briefing:
"Are there “process throttling” capabilities? [Other database products]
have a way to watch for processes that run out-of-control…taking up too many
resource (CPU, Memory) from the rest of the system. I don’t see a way to do
it in 2000. If it's not there, will 2005 have a solution to this?"
Thanks
Kevin Remde
IT Pro Evangelist
Microsoft Corporation
Hi
sp_configure's 'query governor cost limit' can limit the time a quyery runs.
In terms of using other resources, they are ungoverned, even with SQL Server
2005.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Kevin Remde" <KevinRemde@.discussions.microsoft.com> wrote in message
news:A31687BA-155C-47F1-A5CF-538364183408@.microsoft.com...
>A customer asked me at a recent TechNet Briefing:
> "Are there "process throttling" capabilities? [Other database products]
> have a way to watch for processes that run out-of-control.taking up too
> many
> resource (CPU, Memory) from the rest of the system. I don't see a way to
> do
> it in 2000. If it's not there, will 2005 have a solution to this?"
> Thanks
> Kevin Remde
> IT Pro Evangelist
> Microsoft Corporation
Showing posts with label throttling. Show all posts
Showing posts with label throttling. Show all posts
Friday, March 9, 2012
Process throttling
Hi,
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
to
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
to
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
in
of
your processors. that would leave the other processors free to do other stu
ff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user account
s
so that certain users couldn't hog db resources or certain users' queries wo
uld
get higher priority than others.
Microsoft News - SQL Server wrote:
[QUOTE]
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any on
e
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> in
> due
> the
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
quote:|||No.
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
quote:|||Hi Hari,
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
quote:
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
quote:|||if you have a multiprocessor machine, you could limit sql server to a subset
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
due[QUOTE]
> to
the[QUOTE]
>
of
your processors. that would leave the other processors free to do other stu
ff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user account
s
so that certain users couldn't hog db resources or certain users' queries wo
uld
get higher priority than others.
Microsoft News - SQL Server wrote:
[QUOTE]
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any on
e
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> in
> due
> the
Process throttling
Hi,
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have an application accessing an SQL Database. The problem is that
due
> to
> > a flaw in design, when queries are submitted we get 100% cpu usage on
the
> > server. Is there any way to limit processing power for specific access?
> >
> > Thanks
> >
> > Nich
> >
> >
>|||if you have a multiprocessor machine, you could limit sql server to a subset of
your processors. that would leave the other processors free to do other stuff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user accounts
so that certain users couldn't hog db resources or certain users' queries would
get higher priority than others.
Microsoft News - SQL Server wrote:
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any one
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Try to identify the SQL's which is doing a table scan. Then based on the
> > where condition in the select statement
> > create useful indexes, this will definitely reduce the Disk reads, which
> in
> > turn reduces the CPU usage.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> > news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > We have an application accessing an SQL Database. The problem is that
> due
> > to
> > > a flaw in design, when queries are submitted we get 100% cpu usage on
> the
> > > server. Is there any way to limit processing power for specific access?
> > >
> > > Thanks
> > >
> > > Nich
> > >
> > >
> >
> >
We have an application accessing an SQL Database. The problem is that due to
a flaw in design, when queries are submitted we get 100% cpu usage on the
server. Is there any way to limit processing power for specific access?
Thanks
NichHi,
Try to identify the SQL's which is doing a table scan. Then based on the
where condition in the select statement
create useful indexes, this will definitely reduce the Disk reads, which in
turn reduces the CPU usage.
Thanks
Hari
MCDBA
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||No.
Mike Kruchten
"Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have an application accessing an SQL Database. The problem is that due
to
> a flaw in design, when queries are submitted we get 100% cpu usage on the
> server. Is there any way to limit processing power for specific access?
> Thanks
> Nich
>|||Hi Hari,
Thanks, we will be using the Index tune-up wizard in fact, but does any one
know if a physical cpu usage limit can be set in any way apart from using
indexes to maximize table access speed
Thanks
Nich
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Try to identify the SQL's which is doing a table scan. Then based on the
> where condition in the select statement
> create useful indexes, this will definitely reduce the Disk reads, which
in
> turn reduces the CPU usage.
> Thanks
> Hari
> MCDBA
> "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have an application accessing an SQL Database. The problem is that
due
> to
> > a flaw in design, when queries are submitted we get 100% cpu usage on
the
> > server. Is there any way to limit processing power for specific access?
> >
> > Thanks
> >
> > Nich
> >
> >
>|||if you have a multiprocessor machine, you could limit sql server to a subset of
your processors. that would leave the other processors free to do other stuff.
you could also try lowering the priority of the sqlserver process.
i think it would be nice if we could set priority levels for db user accounts
so that certain users couldn't hog db resources or certain users' queries would
get higher priority than others.
Microsoft News - SQL Server wrote:
> Hi Hari,
> Thanks, we will be using the Index tune-up wizard in fact, but does any one
> know if a physical cpu usage limit can be set in any way apart from using
> indexes to maximize table access speed
> Thanks
> Nich
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OMVWVMP5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Try to identify the SQL's which is doing a table scan. Then based on the
> > where condition in the select statement
> > create useful indexes, this will definitely reduce the Disk reads, which
> in
> > turn reduces the CPU usage.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Microsoft News - SQL Server" <naquilina@.gfi.com> wrote in message
> > news:O5yHsJP5DHA.1040@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > We have an application accessing an SQL Database. The problem is that
> due
> > to
> > > a flaw in design, when queries are submitted we get 100% cpu usage on
> the
> > > server. Is there any way to limit processing power for specific access?
> > >
> > > Thanks
> > >
> > > Nich
> > >
> > >
> >
> >
Process Throttle?
Are there any new “process throttling” capabilities in SQL 2005? A customer of mine noted that that Oracle and others have a way to watch for and protect the system from processes that run out-of-control…taking up too many resource (CPU, Memory) from the rest of the system. He didn’t see a way to do it in SQL 2000 and is hoping that 2005 has a solution.
Thanks!
SQL Server 2005 does not have transparent resource management mechanism.
You can build your own custom query submission mechanism using, for example, Service Broker, however, once query is executed, the control over it is the same as in SQL Server 2000 (it is judged by the engine itself based on the cost, memory requested, etc.)
SQL Server 2005 uses advanced memory management and compilation throttling techniques to reduce number of out of memory errors due to different workloads, including runaway queries (there are caps on memory usage based on the amount of available and consumed memory). There is no CPU throttling at the query level.
You can use a set of DMVs to analyze and identify CPU/Memory consumption
|||SQL Server 2005 added MAXDOP option to all index related DDL statements
(see CREATE/ALTER/DROP index and ALTER TABLE ADD/DROP index related constraint. It allows you to specify the degree of parallelism (CPU usage) in some important DDL operations such as index rebuild or index creation. In SQL Server 2000 the engine used all available CPUs.
Subscribe to:
Posts (Atom)