It is possible to method to tell MS SQL that the totally much less important which it may (and really should) take it's the perfect time?
Likewise it is possible to method to tell MS SQL it should give greater priority to some query?
Not in versions below SQL 2008. In SQL Server 2008 there is the resource governor. Using that you could assign logins to groups according to qualities from the login (login title, application title, etc). The particular groups may then be designated to resource pools and restrictions or limitations i.t.o. assets does apply to individuals resource pools
SQL Server doesn't have any type of resource governor yet. There's a collection option known as QUERY_GOVERNOR_COST_LIMIT but it is less than what you are searching for. Also it prevents queries from performing in line with the cost instead of controlling assets.
I am unsure if this sounds like what you are asking, however i were built with a situation in which a single UI click added 10,000 records for an email queue (plenty of data in your body). The e-mail went within the next a few days therefore it did not have to be a higher priority, actually it might bog the server each time it happened.
I split the process into 10,000 individual calls, went the procedure around the UI inside a different thread (set to low priority) and hang it to rest for any second after running the process. It required some time, however i had very granular treatments for what it really was doing.
btw, it was NOT junk e-mail, so don't flame me thinking it had been.