SQL Server 2005 - Priority Queries

Is there any way to give a particular SQL login higher priority for running queries? We have one server, that has multiple databases, unfortunately one of the databases occasionally runs very intensive queries (which aren't too time开发者_C百科 dependant), and it slows down the rest of the databases on the server.

I'd like to be able to tell the server to run queries from a particular login on a higher priority to avoid slow down for other systems.

I understand that typically there would be issues with locking - however in this case, there is one database table that all the databases reference (user information) that is read only - so there wouldn't be any of these issues.

We can't separate out the databases, and we can't add more servers - any ideas?

Thanks

The only way to handle resources in SL 2005 is to create seperate instances, however this only hides memory/cpu from the other instances, it doesnt allow under utilized instances to share its memory/cpu with busy instances.

In SQL Server 2008, they have added the Resource Governor which can prioritise the CPU and Memory based on users or databases (http://msdn.microsoft.com/en-us/library/bb933866.aspx).

Thanks,

Matt