配置 cursor threshold 服务器配置选项Configure the cursor threshold 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 cursor threshold Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the cursor threshold server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. cursor threshold 选项指定游标集中的行数,超过此行数,将异步生成游标键集。The cursor threshold option specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. 当游标为结果集生成键集时,查询优化器会估算将为该结果集返回的行数。When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. 如果查询优化器估算出的返回行数大于此阈值,则将异步生成游标,使用户能够在继续填充游标的同时从该游标中提取行。If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. 否则,同步生成游标,查询将一直等待到返回所有行。Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • SQL ServerSQL Server 不支持异步生成由键集驱动的或静态的 Transact-SQLTransact-SQL 游标。does not support generating keyset-driven or static Transact-SQLTransact-SQL cursors asynchronously. Transact-SQLTransact-SQL 游标操作(如 OPEN 或 FETCH)均为批处理,所以无需异步生成 Transact-SQLTransact-SQL 游标。cursor operations such as OPEN or FETCH are batched, so there is no need for the asynchronous generation of Transact-SQLTransact-SQL cursors. SQL ServerSQL Server 由于每个游标操作都需要进行客户端往返,因此继续支持异步的由键集驱动的或静态的应用程序编程接口 (API) 服务器游标,对于这些游标,OPEN 实现低延迟时间很重要。continues to support asynchronous keyset-driven or static application programming interface (API) server cursors where low latency OPEN is a concern, due to client round trips for each cursor operation.

  • 查询优化器估计键集中行数的准确性取决于游标中每个表统计信息的当前值。The accuracy of the query optimizer to determine an estimate for the number of rows in a keyset depends on the currency of the statistics for each of the tables in the cursor.

建议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.

  • 如果将 游标阙值 设置为 -1,则所有游标键集将同步生成,这对于小游标集很有用。If you set cursor threshold to -1, all keysets are generated synchronously, which benefits small cursor sets. 如果将 cursor threshold 设置为 0,则所有游标键集将异步生成。If you set cursor threshold to 0, all cursor keysets are generated asynchronously. 如果 cursor threshold为其他值,则查询优化器将比较该值与游标集中的所需行数,如果后者大于前者,则将异步生成键集。With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set in cursor threshold. 不要将 cursor threshold 的值设置得过低,因为最好以同步方式创建小结果集。Do not set cursor threshold too low, because small result sets are better built synchronously.

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

配置 cursor threshold 选项To configure the cursor threshold option

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

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

  3. “杂项” 下,将 “游标阈值” 选项更改为所需的值。Under Miscellaneous, change the Cursor Threshold option to the value you want.

使用 Transact-SQLUsing Transact-SQL

配置 cursor threshold 选项To configure the cursor threshold 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_configurecursor threshold 选项设置为 0 ,以便异步生成游标键集。This example shows how to use sp_configure to set the cursor threshold option to 0 so that cursor keysets are generated asynchronously.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'cursor threshold', 0 ;  
GO  
RECONFIGURE  
GO  
  

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

跟进:在配置游标阈值选项之后Follow Up: After you configure the cursor threshold option

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

另请参阅See Also

@@CURSOR_ROWS (Transact-SQL) @@CURSOR_ROWS (Transact-SQL)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
更新统计信息 (Transact-SQL)UPDATE STATISTICS (Transact-SQL)