Execute sql with low priority?

[jenlion]

I have a query that spikes the CPU. Working on optimizing it but that's taking awhile. (It only reads - no writes).

Is it possible to assign a low priority to this query so that other people can get their work done while this runs in background, even it takes longer?

I don't think there is, but hoping somebody here can tell me for sure.

Thanks

 

[markros]

I'm not sure if this is exactly related, but I saw a similar question on another forum.

Here is a suggestion from Paul Mrozowski:
=======================================================
Can you just start it up on a new thread?

C# Version
public class MySample
{
public void DoSomething()
{
// Code runs Stored Proc
}
}

MySample sample = new MySample();
System.Threading.Thread thread = new System.Threading.Thread(sample.DoSomething);
thread.Start();

''VB Version

Public Class MySample
Public Sub DoSomething()
'' Code runs Stored Proc
End Sub
End Class

Dim sample As MySample = New MySample()
Dim thread As System.Threading.Thread = New System.Threading.Thread(sample.DoSomething)
thread.Start()

I use a variation of this for some of the things I do where I fire off an event async:

http://www.rcs-solutions.com/blog/2007/10/19/SpeedingUpMyASPNETApp.aspx

 

[jbenson001]

Unfortunatley, threading won't help on the db end, it will still spike the cpu, event if the db access is on it's own thread.

 

[yelworcm]

In SQL 2008, there is a resource governor. That can cause the SPID to slow down, but I have not played around with it enough to see if it can be applied to individual stored procedures, or if it only affects at a user level.

 

[jenlion]

I saw that about 2008. Unfortunately, I'm in 2000 for now, and only get to upgrade to 2005 soon. 2008 will be years away. :-(