Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
Applies to:
SQL Server
Azure SQL Managed Instance
Creates a resource governor resource pool. A resource pool represents a subset of the physical resources (CPU, memory, and IO) of a Database Engine instance. Resource governor enables you to reserve or limit server resources among resource pools, up to a maximum of 64 pools for SQL Server or 40 for Azure SQL Managed Instance.
Resource governor isn't available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022.
Nóta
To modify resource governor configuration in Azure SQL Managed Instance, you must be in the context of the master
database on the primary replica.
Transact-SQL syntax conventions
CREATE RESOURCE POOL pool_name
[ WITH
(
[ MIN_CPU_PERCENT = value ]
[ [ , ] MAX_CPU_PERCENT = value ]
[ [ , ] CAP_CPU_PERCENT = value ]
[ [ , ] AFFINITY {SCHEDULER =
AUTO
| ( <scheduler_range_spec> )
| NUMANODE = ( <NUMA_node_range_spec> )
} ]
[ [ , ] MIN_MEMORY_PERCENT = value ]
[ [ , ] MAX_MEMORY_PERCENT = value ]
[ [ , ] MIN_IOPS_PER_VOLUME = value ]
[ [ , ] MAX_IOPS_PER_VOLUME = value ]
)
]
[;]
<scheduler_range_spec> ::=
{ SCHED_ID | SCHED_ID TO SCHED_ID }[,...n]
<NUMA_node_range_spec> ::=
{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID }[,...n]
Is the user-defined name for the resource pool. pool_name is alphanumeric, can be up to 128 characters, must be unique within a Database Engine instance, and must comply with the rules for Database identifiers.
Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there's CPU contention. value is an integer with a default setting of 0. The allowed range for value is from 0 through 100.
Specifies the maximum average CPU bandwidth that all requests in resource pool receive when there's CPU contention. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Applies to: SQL Server 2012 (11.x) and later.
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Nóta
Due to the statistical nature of CPU governance, you might notice occasional short spikes exceeding the value specified in CAP_CPU_PERCENT
.
Applies to: SQL Server 2012 (11.x) and later.
Attach the resource pool to specific schedulers. The default value is AUTO
.
Specifying <scheduler_range_spec>
for AFFINITY SCHEDULER
affinitizes the resource pool to the Database Engine schedulers identified by the given IDs. These IDs map to the values in the scheduler_id
column in sys.dm_os_schedulers.
Specifying <NUMA_node_range_spec>
for AFFINITY NUMANODE
affinitizes the resource pool to the Database Engine schedulers that map to the logical CPUs that correspond to the given NUMA node or a range of nodes. You can use the following Transact-SQL query to discover the mapping between the physical NUMA configuration and the Database Engine scheduler IDs.
SELECT osn.memory_node_id AS numa_node_id,
sc.cpu_id,
sc.scheduler_id
FROM sys.dm_os_nodes AS osn
INNER JOIN sys.dm_os_schedulers AS sc
ON osn.node_id = sc.parent_node_id
AND
sc.scheduler_id < 1048576;
Specifies the minimum amount of query workspace memory reserved for the resource pool that can't be shared with other resource pools. value is an integer with a default setting of 0. The allowed range for value is from 0 to 100.
Specifies the maximum amount of query workspace memory that requests in this resource pool can use. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Applies to: SQL Server 2014 (12.x) and later.
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool. The allowed range for value is from 0 through 2^31-1 (2,147,483,647). Specify 0 to indicate no minimum for the pool. The default is 0.
Applies to: SQL Server 2014 (12.x) and later.
Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for value is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.
If the MAX_IOPS_PER_VOLUME
for a pool is set to 0, the pool isn't IO-governed at all and can take all the IOPS in the system even if other pools have MIN_IOPS_PER_VOLUME
set. For this case, we recommend that you set the MAX_IOPS_PER_VOLUME
value for this pool to a high number (for example, the maximum value 2^31-1) to make this pool IO-governed and to honor the IOPS reservations that might exist for other pools.
The sum of MIN_CPU_PERCENT
or MIN_MEMORY_PERCENT
for all resource pools can't exceed 100 percent.
MIN_IOPS_PER_VOLUME
and MAX_IOPS_PER_VOLUME
specify the minimum and maximum IOs per second. The IOs can be either reads or writes, and can be of any size. Therefore, with the same IOPS limits, the minimum and maximum IO throughput can vary depending on the mix of IO sizes in the workload.
The values for MAX_CPU_PERCENT
and MAX_MEMORY_PERCENT
must be greater than or equal to the values for MIN_CPU_PERCENT
and MIN_MEMORY_PERCENT
, respectively.
CAP_CPU_PERCENT
differs from MAX_CPU_PERCENT
in that workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT
if it's available, but not above the value of CAP_CPU_PERCENT
. Although there might be short spikes higher than CAP_CPU_PERCENT
, workloads can't exceed CAP_CPU_PERCENT
for extended periods of time, even when additional CPU capacity is available.
The total CPU percentage for each affinitized component (scheduler(s) or NUMA node(s)) can't exceed 100 percent.
For more information, see Resource governor and Resource governor resource pool.
Requires the CONTROL SERVER
permission.
For additional resource governor configuration examples, see Resource governor configuration examples and best practices.
This example created a resource pool named bigPool
. This pool uses the default resource governor settings.
CREATE RESOURCE POOL bigPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;
This example configures the adhocPool
resource pool as follows:
MIN_CPU_PERCENT
and MIN_MEMORY_PERCENT
respectively.MAX_MEMORY_PERCENT
.MAX_CPU_PERCENT
and CAP_CPU_PERCENT
respectively.AFFINITY SCHEDULER
.Applies to: SQL Server 2012 (11.x) and later.
CREATE RESOURCE POOL adhocPool
WITH (
MIN_CPU_PERCENT = 10,
MAX_CPU_PERCENT = 20,
CAP_CPU_PERCENT = 30,
MIN_MEMORY_PERCENT = 5,
MAX_MEMORY_PERCENT = 15,
AFFINITY SCHEDULER = (0 TO 63, 128 TO 191)
);
This example reserves 200 IOPS per volume for the pool using MIN_IOPS_PER_VOLUME
, and limits the IOPS per volume to 1000 using MAX_IOPS_PER_VOLUME
. These values govern the total physical I/O read and write operations that are available for requests using the resource pool.
Applies to: SQL Server 2014 (12.x) and later.
CREATE RESOURCE POOL PoolAdmin
WITH (
MIN_IOPS_PER_VOLUME = 200,
MAX_IOPS_PER_VOLUME = 1000
);
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniu