SQL server limit resources for a stored procedure

Question

I have a stored procedure that takes up too much of server resources. I want to limit this query to only use no more than, say, 10% of the reosurces. I've heard of Resource governor, but it is only available for enterprise and developer editions. I have a standard edition. Is there any other alternatives to this, except for buying a better version of sql server?

Answer 1

Define 'resources'. IO? CPU? Memory? Network? Contention? Log size? tempdb? And you cannot answer 'all'. You need to specify what resources are being consumed now by the procedure.

Your question is too generic to be answered. There is no generic mechanism to limit 'resources' on a query or procedure, even the Resource Governor only limits some resources. You describe your problem as is a high volume data manipulation for a long period of time like tens of thousands of inserts updates throughout the database which would indicate toward batching the processing. If the procedure does indeed what you describe then throttling its execution is probably not what you want, because you want to reduce the duration of the transactions (batches) not increase it.

And first and foremost: did you analyzed the procedure as procedure resource consumption as a performance problem, using a methodology like Waits and Queues? Only after you done so and the procedure runs optimally (meaning it consumes the least resources required to perform it's job) can you look into throttling the procedure (and most likely by that time the need to throttle has magically vanished).

Answer 2

You can use the MAXDOP query hint to restrict how many CPUs a given query can utilize. Barring the high-end bells and whistles you mentioned, I don't think there's anything that lets you put single-process-level constraints on memory allocation or disk usage.