Limit size and/or frequency of user queries in SQL Server

Question

Is it possible to put a cap on the "size" and frequency of user queries in SQL Server (or perhaps another engine)? For example:

Let's say there are a few tables with millions of rows. Maybe there's a handful of admins and analysts working on the tables, and they'd know their way around enough to not run any unnecessary heavy queries that may run for several minutes/hours.

However, a sales/marketers/admin staff less familiar with SQL is more likely to run a heavy query e.g. with loads of joins, whether accidently or just for the fun of it. Multiply this by dozens of them, and the server can be severely hammered at.

Ideally, I'd want restrictions like the following:

1. If the engine anticipates there'll be above a million row scans, cancel the query (and tell the user why it was cancelled).
2. Limit queries being run by a single user to 20 queries within a 10-minute window.
3. User/role-level "caps"

Answer 1

The feature you're looking for is called Resource Governor.

You can classify incoming connections and assign them to a Workload Group, which specifies the resource access policy

CREATE WORKLOAD GROUP group_name
[ WITH
    ( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
      [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
      [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
      [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
      [ [ , ] MAX_DOP = value ]
      [ [ , ] GROUP_MAX_REQUESTS = value ] )
 ]
[ USING {
    [ pool_name | "default" ]
    [ [ , ] EXTERNAL external_pool_name | "default" ] ]
    } ]
[ ; ]

And maps to a Resource Pool which has limited access to server resources.

In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups.

It's important to combine Resource Governor with snapshot-based reads for the reporting users, either using SNAPSHOT isolation, or by setting the database to READ_COMMITTED_SNAPSHOT. Otherwise a reporting user with limited access to resources can acquire locks that interfere with other workloads.