Workload management in Analytics Platform System
SQL Server PDW's workload management capabilities allow users and administrators to assign requests to pre-set configurations of memory, and concurrency. Use workload management to improve performance of your workload, either consistent or mixed, by allowing requests to have the appropriate resources without starving any requests forever.
For example, with the workload management techniques in SQL Server PDW, you could:
Allocate a large number of resources to a load job.
Specify more resources for building a columnstore index.
Troubleshoot a slow-performing hash join to see if it needs more memory, and then give it more memory.
Workload Management Basics
Key Terms
Workload Management
Workload Management is the ability to understand and adjust system resource utilization in order to achieve the best performance for concurrent requests.
Resource Class
In SQL Server PDW, a resource class is a built-in server role that has pre-assigned limits for memory and concurrency. SQL Server PDW allocates resources to requests according to the resource class server role membership of the login that submits the requests.
On the Compute nodes, the implementation of resource classes uses the Resource Governor feature in SQL Server. For more information about Resource Governor, see Resource Governor on MSDN.
Understand Current Resource Utilization
To understand system resource utilization for the currently running requests, use the SQL Server PDW dynamic management views. For example, you can use DMVs to understand if a slow-running large hash join could benefit by having more memory.
Adjust Resource Allocations
To adjust resource utilization, change the resource class membership of the login that is submitting the request. The resource class server roles are named mediumrc, largerc, and xlargerc. They represent medium, large, and extra large resource allocations respectively.
For example, to allocate a large amount of system resources to a request, add the login that is submitting the request to the largerc server role. The following ALTER SERVER ROLE statement adds the login Anna to the largerc server role.
ALTER SERVER ROLE largerc ADD MEMBER Anna;
Resource Class Descriptions
The following table describes the resource classes and their system resource allocations.
| Resource Class | Request Importance | Maximum Memory Usage* | Concurrency Slots (Maximum = 32) | Description |
|---|---|---|---|---|
| default | Medium | 400 MB | 1 | By default, each login is allowed a small amount of memory, and concurrency resources for its requests. When a login is added to a resource class, the new class takes precedence. When a login is dropped from all resource classes, the login reverts back to the default resource allocation. |
| MediumRC | Medium | 1200 MB | 3 | Examples of requests that might need the medium resource class: CTAS operations that have large hash joins. SELECT operations that need more memory to avoid caching to disk. Loading data into clustered columnstore indexes. Building, rebuilding, and reorganizing clustered columnstore indexes for smaller tables that have 10-15 columns. |
| Largerc | High | 2.8 GB | 7 | Examples of requests that might need the large resource class: Very large CTAS operations that have huge hash joins, or contain large aggregations, such as large ORDER BY or GROUP BY clauses. SELECT operations that require very large amounts of memory for operations such as hash joins, or aggregations such as ORDER BY or GROUP BY clauses Loading data into clustered columnstore indexes. Building, rebuilding, and reorganizing clustered columnstore indexes for smaller tables that have 10-15 columns. |
| xlargerc | High | 8.4 GB | 22 | The extra large resource class is for requests that could require extra large resource consumption at run time. |
*Maximum memory usage is an approximation.
Request Importance
The request importance maps to the amount of CPU time that SQL Server, running on the Compute nodes, will give to the requests. Requests with higher priority receive more CPU time.
Maximum Memory Usage
Maximum memory usage is the maximum amount of available memory a request can use within each processing space. For example a mediumrc request can use up to 1200 MB for processing within each distribution. It is still important to ensure data is not skewed in order to avoid having a few distributions performing most of the work.
Concurrency Slots
The goal of allocating 1, 3, 7, and 22 concurrency slots is to allow both large and small processes to run at the same time, without blocking small process when a large process is running. For example, SQL Server PDW can allocate maximum of 32 concurrency slots to run 1 extra large request (22 slots), 1 large request (7 slots), and 1 medium request (3 slots) at the same time.
Examples of allocating up to 32 concurrency slots to concurrent requests:
28 slots = 4 large
30 slots = 10 medium
32 slots = 32 default
32 slots = 1 extra large + 1 large + 1 medium
32 slots = 2 large + 4 medium + 6 default
Suppose 6 large requests are submitted to SQL Server PDW, and then 10 default requests are submitted. SQL Server PDW will process the requests in priority order as follows:
Allocate 28 concurrency slots to start processing 4 large requests as memory becomes available, and keep 2 large requests in the queue.
Allocate 4 concurrency slots to start processing 4 default requests and keep 6 default requests in the wait queue.
As requests finish and concurrency slots become available, SQL Server PDW will allocate the remaining requests according to available resources and priority. For example, when there are 7 concurrency slots open, waiting large requests will have higher priority for the 7 slots than waiting medium requests. If 6 slots open, then SQL Server PDW will allocate 6 more default-sized requests. However, memory and concurrency slots must all be available before SQL Server PDW allows a request to run.
Within each resource class, the requests run in first in first out (FIFO) order.
General Remarks
If a login is a member of more than one resource class, the class with the most resources takes precedence.
When a login is added to or dropped from a resource class, the change takes effect immediately for all future requests; current requests that are running or waiting are not affected. The login does not need to disconnect and reconnect in order for the change to occur.
For each login, the resource class settings are applied to individual statements and operations, and not to the session.
Before SQL Server PDW runs a statement, it tries to acquire the concurrency slots needed for the request. If it cannot acquire enough concurrency slots, SQL Server PDW moves the request into a waiting-to-be-executed state. All resources system that were already allocated to the request are returned back to the system.
Most of the SQL statements always need the default resource allocations, and therefore are not controlled by resource classes. For example, CREATE LOGIN only needs a small amount of resources, and is allocated the default resources even if the login calling CREATE LOGIN is a member of a resource class. For example, if Anna is a member of the largerc resource class and she submits a CREATE LOGIN statement, the CREATE LOGIN statement will run with the default number of resources.
SQL statements and operations governed by resource classes:
ALTER INDEX REBUILD
ALTER INDEX REORGANIZE
ALTER TABLE REBUILD
CREATE CLUSTERED INDEX
CREATE CLUSTERED COLUMNSTORE INDEX
CREATE TABLE AS SELECT
CREATE REMOTE TABLE AS SELECT
Loading data with dwloader.
INSERT-SELECT
UPDATE
DELETE
RESTORE DATABASE when restoring into an appliance with more Compute nodes.
SELECT, excluding DMV-only queries
Limitations and Restrictions
The resource classes govern memory and concurrency allocations. They do not govern input/output operations.
Metadata
DMVs that contain information about resource classes and resource class members.
DMVs that contain information about the state of requests and the resources they require:
Related system views exposed from the SQL Server DMVs on the Compute nodes. See SQL Server Dynamic Management Views for links to these DMVs on MSDN.
sys.dm_pdw_nodes_resource_governor_resource_pools
sys.dm_pdw_nodes_resource_governor_workload_groups
sys.dm_pdw_nodes_resource_governor_resource_pools
sys.dm_pdw_nodws_resource_governor_workload_groups
sys.dm_pdw_nodes_exec_sessions
sys.dm_pdw_nodes_exec_requests
sys.dm_pdw_nodes_exec_query_memory_grants
sys.dm_pdw_nodes_exec_query_resource_semaphores
sys.dm_pdw_nodes_os_memory_brokers
sys.dm_pdw_nodes_os_memory_cache_entries
sys.dm_pdw_nodes_exec_cached_plans