Question: Is there any way for me to allow queries from certain users to have higher priority than queries from other users?
Answer: Yes, using Resource Governor in Enterprise Edition of SQL Server 2008 onwards.
Most people know that Resource Governor allows you to specify CPU and memory limits when contention for these resources occurs but it’s a little-known fact that you can configure relative priorities as well. (Note that what memory is able to be governed expanded in SQL Server 2012, and the ability to put a hard, permanent cap on CPU usage was introduced too).
Related: Configuring SQL Server 2008's Resource Governor
Resource Governor allows workload groups that use the same resource pool to have relative priorities. In English, this means that can have a set of connections to SQL Server that will be able to execute at a higher priority than another set of connections.
This can be done by setting the IMPORTANCE setting when creating or altering a workload group. There are three settings: HIGH, MEDIUM, and LOW. These settings affect how the Runnable Queue of the SQLOS thread schedulers work.
Usually the Runnable Queue is a true FIFO (First-In-First-Out) queue. When a thread on the Waiter List is signaled that it’s resource is available, it goes to the bottom of the Runnable Queue. Similarly when a running thread exhausts its 4 millisecond quantum and yields – it goes to the bottom of the Runnable Queue.
This changes when workload group priorities are involved. The HIGH, MEDIUM, and LOW priorities equate to a 9 to 3 to 1 thread ratio. This means that for every 1 LOW priority thread, the scheduler will allow 9 HIGH priority threads to execute before the next LOW priority thread can execute.
This means that if there has just been a LOW priority thread executing, and there is a LOW priority thread on the Runnable Queue, the scheduler will allow 3 MEDIUM and 9 HIGH priority threads to enter the Runnable Queue above the LOW priority thread. If a tenth HIGH priority thread becomes able to run, it will have to go below the LOW priority thread on the Runnable Queue. And so on.
Using this mechanism you can introduce prioritization for workloads as well as governing resources like CPU and memory. Be aware though, that this is a pretty advanced configuration change to make so you really want to make sure you’ve tested the behavior in a test environment before introducing it into production.
