SQL Server Performance

Limit CPU used by a sp or a user account

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by WingSzeto, Nov 14, 2006.

  1. WingSzeto Member

    I am testing SQL 2K5 with sp1.

    We have a stored procedure that is using a lot of CPU resource. This sp is run by a specific SQL user account. Is there a way I can limit the resource (CPU) being used by this sp. For example, if the system detects this SP or the user account, this sp can only use 20% of the CPU. Any help is very much appreciated.

    wingman
  2. thomas New Member

    I don't think this is possible. you can use Query Governor Cost Limit, from Books Online:-

    "Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration."

    but this will just stop a query if it meets the limit, not limit its use of processors.

    Maybe you should look at stopping parallelism (assuming you have multiple processors). you could stop this query from executing in parallel by using the WITH (MAXDOP =1) optimiser hint, which will at least ensure it only hogs one processor.

    But you can't limit it to say 20% of the processor power. I don't think.

Share This Page