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.