Prevent SQL queries from unprivileged users from blocking critical processes

Question

I have a system that allows users to log in and construct queries to run against a SQL 2012 server. The system needs to be pretty liberal in what queries it allows, so I'm just relying on SQL grants to prevent users from doing things they aren't supposed to.

I'm worried that something like priority inversion could still occur. Say a unprivileged user logs in, finds a table to abuse and runs a dozen or so outer joins on it. Or simply begins a transaction and never closes it.

I want to ensure that unprivileged users cannot block critical processes from more privileged users. I'm thinking of using the Resource Governor to do this, but I'm not sure if this is the best way of if there are other issues I might need to worry about.

What's the best way to ensure that queries from unprivileged users cannot block critical processes?

Answer 1

We've run into similar requirements and ultimately turned to database snapshots on our failover SQL Server 2008 boxes for unpriviledged users. That gave us some priority segregation and allowed us to make better use of our failover hardware.

Now that we're on SQL Server 2012, we're looking into AlwaysOn. It's not perfect, but if you have a HA environment, it's worth considering. There's a AlwaysOn whitepaper available at the bottom of this page: http://www.microsoft.com/en-us/sqlserver/solutions-technologies/mission-critical-operations/high-availability.aspx.

Answer 2

Resource Governor can only limit the resources of an arbitrary user (memory, CPU). It cannot prevent priority inversion, and if priority inversion happens the resource governor will only make it last longer.

If users can cause blockage then you must move the users to a safe playground (eg. a standby replica) or you must actively monitor for blockage. There's not much room to maneuver. If these users only do R/O operations then you can try use snapshot isolation to avoid blockage.