SQL Server 2005 - Priority Queries

Question

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 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

Answer 1

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