Bagikan melalui

  • Artikel

Resource Governor DDL and System Views

You can use Resource Governor DDL commands, catalog views, and dynamic management views for all aspects of Resource Governor. In addition to new views that are specific to Resource Governor, existing system views have been modified to include information about Resource Governor. This topic summarizes the following statements and views:

  • DDL commands, including commands for workload groups, resource pools, and Resource Governor.

  • Resource Governor system views, including catalog and dynamic management views.

  • SQL Server system views that are updated to support Resource Governor.

  • SQL Server event class updates as well as new event classes to support Resource Governor.

DDL Statements

Resource Governor provides seven DDL commands that you can use to configure all aspects of a Resource Governor session. There are CREATE, ALTER, and DROP commands for workload groups and resource pools. There is also an ALTER RESOURCE GOVERNOR RECONFIGURE statement that applies configuration changes.

The key things to note about Resource Governor DDL commands are as follows:

  • The CREATE, ALTER, and DROP statements work on the stored metadata and are transactional. However, completion of these statements does not make the changes effective; you must execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement to apply changes.

  • The preceding statements can be part of a transaction to prevent simultaneous modifications to the same configuration by multiple users. This is done by using BEGIN TRANSACTION and COMMIT TRANSACTION with the statements. Any locks held on a transaction are visible in sys.dm_tran_locks and the transaction can be stopped if necessary.

    Note

    Although not required, we strongly recommend that you use transactional syntax when using Resource Governor DDL statements. However, ALTER RESOURCE GOVERNOR DISABLE, ALTER RESOURCE GOVERNOR RECONFIGURE and ALTER RESOURCE GOVERNOR RESET STATISTICS cannot be part of a user transaction.

  • The ALTER…RECONFIGURE statement loads the metadata into memory and may fail. You cannot roll back the statement. If the statement fails, the changes to the metadata are not copied to memory. If the statement succeeds, the changes are effective. ALTER…RECONFIGURE is not transactional, and changes cannot be rolled back.

Resource Pool Commands

Use the commands described in the following table to configure resource pools.

DDL statement

Description

CREATE RESOURCE POOL

Creates a resource pool.

ALTER RESOURCE POOL

Changes the configuration of a resource pool. This statement writes the changes to the stored configuration metadata. After executing this statement, you must execute the ALTER RESOURCE GOVERNOR statement to write the metadata information to the in-memory configuration.

DROP RESOURCE POOL

Drops a resource pool.

Resource Governor provides four parameters for configuring a resource pool. They are:

  • A minimum and maximum percentage for CPU use.

  • A minimum and maximum percentage for memory use.

Depending on the change that you make to a resource pool configuration there may be a transition period and known behaviors for handling active or waiting requests during this transition. For more information, see Resource Governor States.

Workload Group Commands

Use the commands described in the following table to configure workload groups.

DDL command

Description

CREATE WORKLOAD GROUP

Creates a workload group and associates it with a resource pool.

ALTER WORKLOAD GROUP

Changes the configuration of a workload group. This statement writes the changes to the stored configuration metadata. After executing this statement, you must execute the ALTER RESOURCE GOVERNOR statement to write the metadata information to the in-memory configuration.

DROP WORKLOAD GROUP

Drops a workload group.

Resource Governor provides the following six parameters for configuring a workload group:

  • The maximum amount of memory for a request.

  • The maximum percentage of CPU to use for a request.

  • The resource time-out for a request.

  • The relative importance of a request.

  • The maximum number of requests for a workload group.

  • The resource pool that the workload group will be in.

Depending on the change that you make to a workload group configuration, there are known behaviors for handling active or waiting requests when changes are applied. For more information, see Resource Governor States.

Resource Governor Command

Use the command described in the following table to configure Resource Governor.

DDL command

Description

ALTER RESOURCE GOVERNOR

Applies configuration changes specified with an ALTER command, enables or disables RESOURCE GOVERNOR, registers a classifier function, or resets statistics.

The ALTER RESOURCE GOVERNOR statement is used to perform the following actions:

  • Applying the configuration changes that are specified when either ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements are issued.

  • Enabling or disabling Resource Governor. This is also used for applying configuration changes.

  • Registering a function for request classification.

  • Resetting statistics on all workload groups and resource pools.

Depending on the change that you make to a Resource Governor configuration, there are known behaviors for handling active or waiting requests when changes are applied. For more information, see Resource Governor States.

Resource Governor Views

The following catalog and dynamic management views are specific to Resource Governor.

Catalog Views

Resource Governor catalog views are described in the following table.

Name

Description

sys.resource_governor_configuration

Returns the stored Resource Governor state.

sys.resource_governor_resource_pools

Returns the stored resource pool configuration. Each row of the view determines the configuration of a pool.

sys.resource_governor_workload_groups

Returns the stored workload group configuration.

Dynamic Management Views

Resource Governor dynamic management views are described in the following table.

Name

Description

sys.dm_resource_governor_workload_groups

Returns workload group statistics and the current in-memory configuration of the workload group.

sys.dm_resource_governor_resource_pools

Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.

sys.dm_resource_governor_configuration

Returns a row that contains the current in-memory configuration state for Resource Governor.

SQL Server Views

Several SQL Server system views have been updated and provide information about Resource Governor.

Dynamic Management Views

SQL Server dynamic management views are described in the following table.

Name

Description

sys.dm_exec_query_memory_grants

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view. The following columns are added for Resource Governor:

  • group_id

  • pool_id

  • is_small

  • ideal_memory_kb

sys.dm_exec_query_resource_semaphores

Returns the information about the current query-resource semaphore status. sys.dm_exec_query_resource_semaphores provides general query-execution memory status and allows you to determine whether the system can access enough memory.

The following column is added for Resource Governor:

  • pool_id

sys.dm_exec_sessions

Returns one row per authenticated session on SQL Server.

The following column is added for Resource Governor:

  • group_id

sys.dm_exec_requests

Returns information about each request that is executing within SQL Server.

The following column is added for Resource Governor:

  • group_id

sys.dm_exec_cached_plans

Returns a row for each query plan that is cached by SQL Server for faster query execution.

The following column is added for Resource Governor:

  • pool_id

sys.dm_os_memory_brokers

Allocations that are internal to SQL Server use the SQL Server memory manager.

The following columns are added for Resource Governor:

  • pool_id

  • allocations_kb_per_sec

  • predicated_allocations_kb

  • overall_limit_kb

sys.dm_os_wait_stats

Returns information about the waits encountered by threads that are in execution. You can use this view to diagnose performance issues with SQL Server and also with specific queries and batches.

SQL Server Event Class Reference

Several SQL Server event classes have been updated and new event classes have been added to support Resource Governor.

Name

Description

CPU Threshold Exceeded Event Class

This event class indicates that Resource Governor detects a query that exceeds the CPU threshold specified for REQUEST_MAX_CPU_TIME_SEC.

PreConnect:Starting Event Class

This event class indicates when a LOGON trigger or Resource Governor classifier function starts executing.

PreConnect:Completed Event Class

This event class indicates when a LOGON trigger or Resource Governor classifier function finishes executing.