Delay a SQL Server 2000 query until processor is below 50% utilization using a query or sproc?

I have a several very expensive queries which seem t开发者_如何学运维o hog resources that seems to put the system over the top.

Is there a delay function I can call to wait until processor resources come back down in SQL Server 2000 - 2008?

My eventual goal is to go back and make these more efficient, use a sproc, but in the meantime I need to get these to work asap because I'm rewriting legacy code.

---

you could try something like this:

DECLARE @Busy int
       ,@Ticks int
SELECT @Busy=@@CPU_BUSY
      ,@Ticks=7777  --<you have to determine this value based on your machine
WAITFOR DELAY '00:00:10' --10 seconds

WHILE @@CPU_BUSY-@Ticks>@BUSY
BEGIN
    --too busy, wait longer
    @BUSY=@@CPU_BUSY
    WAITFOR DELAY '00:00:10' --10 seconds
END

EXEC YourProcedureHere

to determine the @Ticks value, just write a loop to print out the difference between @@CPU_BUSY values every 10 seconds. When the system is at your low load, use this value as @Ticks.

---

You can't control or throttle CPU except for higher editions of SQL Server 2008.

Your best option seems to be to set options to allow only half (or less) your CPUs to be used for any query. This can be done 2 ways

• at the server level for all queries using "max degree of parallelism Option"
• per query, for offending query with MAXDOP hint

Also see:

• KB article "General guidelines to use to configure the MAXDOP option"
• SO question: Control the CPU usage during TSQL query- sql 2008 (not a duplicate of this)

Edit:

The question would be: do you want to delay execution (with all the issues like CommandTimneout, user response time etc) or improve concurrency for all queries

This answer should improve concurrency: I usually deal with client apps and I can't make a business user wait.

When delaying execution, you also have to delay all queries (say to disallow the expensive queries from running) which reduce concurrency throughout as calls will back up. And you'll have to be careful about 2 expensive queries starting around the same time

---

The only thing I can think of here to actually kick things off in quiet times is to use scheduled tasks and osql to execute your statements. Scheduled tasks has to option to run when idle.

I'm not sure about the 50% bit though.

This strategy shouldn't be too sensitive to SQL version either.