Process Throttling

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

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!
Kevin Remde

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.