SQL Server Performance Forum – Threads Archive
Limit CPU used by a sp or a user account
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. wingmanI 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.
]]>