配置并行的开销阈值服务器配置选项Configure the cost threshold for parallelism Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明了如何使用 SQL ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio “并行的开销阈值” Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the cost threshold for parallelism server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. cost threshold for parallelism 选项指定 SQL ServerSQL Server 创建和运行并行查询计划的阈值。The cost threshold for parallelism option specifies the threshold at which SQL ServerSQL Server creates and runs parallel plans for queries. 仅当运行同一查询的串行计划的估计开销高于在“并行的开销阈值”中设置的值时,SQL ServerSQL Server 才创建和运行该查询的并行计划。SQL ServerSQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. 成本指的是在特定硬件配置中运行串行计划估计需要花费的成本,而不是时间单位。The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. “并行的开销阈值” 选项可设置为 0 到 32767 之间的任何值。The cost threshold for parallelism option can be set to any value from 0 through 32767. 默认值为 5。The default value is 5.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 成本是指成本的抽象单位,而不是预估时间的单位。The cost refers to an abstracted unit of cost and not a unit of estimated time. 只能为对称多处理器设置 cost threshold for parallelismOnly set cost threshold for parallelism on symmetric multiprocessors.

  • SQL ServerSQL Server 将忽略 的值:ignores the cost threshold for parallelism value under the following conditions:

    • 计算机只有一个逻辑处理器。Your computer has only one logical processor.

    • 由于 SQL ServerSQL Server 相关性掩码 配置选项的原因,只有一个逻辑处理器可供 使用。Only a single logical processor is available to SQL ServerSQL Server because of the affinity mask configuration option.

    • “最大并行度” 选项设置为 1。The max degree of parallelism option is set to 1.

逻辑处理器是处理器硬件的基本单元,可让操作系统调度任务或执行线程上下文。A logical processor is the basic unit of processor hardware that allows the operating system to dispatch a task or execute a thread context. 每个逻辑处理器一次只执行一个线程上下文。Each logical processor can execute only one thread context at a time. 处理器内核是提供解码和执行指令的能力的电路。The processor core is the circuitry that provides ability to decode and execute instructions. 一个处理器内核可能包含一个或多个逻辑处理器。A processor core may contain one or more logical processors. 以下 Transact-SQLTransact-SQL 查询可用于获取系统的 CPU 信息。The following Transact-SQLTransact-SQL query can be used for obtaining CPU information for the system.

SELECT (cpu_count / hyperthread_ratio) AS PhysicalCPUs,   
cpu_count AS logicalCPUs   
FROM sys.dm_os_sys_info  

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 在某些情况下,即使查询的开销计划小于当前 “并行的开销阈值” 的值,也有可能选择并行计划。In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. 出现这种情况,是因为使用并行还是串行计划是根据先前优化过程所提供的开销估计确定的。This can happen because the decision to use a parallel or serial plan is based on a cost estimate provided earlier in the optimization process. 有关详细信息,请参阅查询处理体系结构指南For more information, refer to the Query Processing Architecture Guide.

  • 虽然对于大多数系统而言,默认值 5 已足够,但可能需要不同的值。While the default value of 5 is adequate for most systems, a different value may be appropriate. 如果需要,可以使用高值和低值执行应用程序测试,以优化应用程序性能。Perform application testing with higher and lower values if needed to optimize application performance.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置并行的开销阈值选项To configure the cost threshold for parallelism option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “高级” 节点。Click the Advanced node.

  3. 在“并行”下,将“并行的开销阈值”选项更改为所需值。Under Parallelism, change the Cost Threshold for Parallelism option to the value you want. 键入或选择一个值(介于 0 到 32767 之间)。Type or select a value from 0 to 32767.

使用 Transact-SQLUsing Transact-SQL

配置并行的开销阈值选项To configure the cost threshold for parallelism option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configurecost threshold for parallelism 选项的值设置为 10This example shows how to use sp_configure to set the value of the cost threshold for parallelism option to 10.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'cost threshold for parallelism', 10 ;  
GO  
RECONFIGURE  
GO  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置并行的开销阈值选项之后Follow Up: After you configure the cost threshold for parallelism option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

配置并行索引操作 Configure Parallel Index Operations
查询提示 (Transact-SQL) Query Hints (Transact-SQL)
ALTER WORKLOAD GROUP (Transact-SQL) ALTER WORKLOAD GROUP (Transact-SQL)
affinity mask 服务器配置选项 affinity mask Server Configuration Option
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)