Each executing query/tran is split into so-we-call "quants". The quant is the time slice spent on CPU. Bigger the query is, more quants will be used. The quants' time (in microseconds) configured in the registry:
The value can be changed, but requires restart.
The blocking time never counts (waiting on lock, for disk IO, network IO). It will suspend the current quant, and execute another ready quant. When block is removed, the quant becomes ready, and, will be scheduled by CPU again. <br>For example, the simple select:
SELECT * FROM [mytable] where [key] = x
will use 1-2 quants depending on the database size, and,
may use 100 or more quants.
The quants can be executed with the different priorities, and, under stress, will affect the query response time. There are 4 priorities: [Highest],[Higher],[Normal],and [Low]. The database engine will schedule quants first from [Highest] priority queue, if queue is empty, from [Higher], then from [Normal], and [Low]. The [Normal] priority queue every 10th time will schedule [Low] priority quant, even if [Normal] is not empty. Hence, [Highest] and [Higher] queues must be empty before proceeding to [Normal],[Low].
The amount of quants, consumed by query, will be received after each clients' (e.g. NET provider) execution, and, stored in the system table [msessions] (fields: quant_0 (low),quant_1,quant_2,quant_3(highest)). Also, if database has been secured, the aggregated quants usage will be store in the system table [musers].
By manipulating each quant's priority, the overall query priority can be created. For example, mixing query quants, such some quants assigned [Normal] priority and some [Low], will make the query priority lower than the one with all quants executed as [Normal].
The query priority ranges [1,100], and, 128 (High), 256 (Highest). By default, administrators' queries executed with 128 priorities. Higher priority, faster the query will respond. For instance, reporting queries may run with lower priority than business logic as followed are critical. The query prioritization activated when database is under the load, otherwise running with priority 1 or 100 will make no difference.
The query priority can be set in .NET providers' connection string, e.g. "...Priority=50;...". When priority attribute is omitted, the default value (100) used.
After executing set priority command, the followed quants will be scheduled according new query priority. The example bellow, set query priority to 70:
SET PRIORITY 70 --do sql--
When database is secure, the performance roles can be used to control query priorities for a group of users.
For example, create the performance role [myperfrole], execute SQL command:
CREATE PERFORMANCE ROLE [myperfrole] PRIORITY 70
Next, add role to the user [myuser]:
ADD PERFORMANCE ROLE [myperfrole] TO USER [myuser]
After [Myuser] becomes a member of performance role [myperfrole], the users' query priority will be controlled by the role. In addition, the performance role, can alter the priority, affecting all member users:
ALTER PERFORMANCE ROLE [myperfrole] PRIORITY 80
Query throttling allows dynamically decreases the query priority based on the used resources (disk, memory, cpu, network). This is useful to prevent a single clients' transaction affecting performance of other concurrent transactions running with the same priority. Worse the query is, the priority should decrease. The throttling decreases the priority using exponential decay "half-life", where configured number of resources must be consumed before priority half.
The query "half-life" parameters configured in the registry. They are used to calculate resource usage and decrease query priority.Read more...
Additionally, if database has been secured, the "half-life" can be configured through performance roles. Read more....
The priority throttling can be disabled/enabled per query:
SET THROTTLE ON/OFF -- do sql --