sys.sp_xtp_control_query_exec_stats (Transact-SQL)sys.sp_xtp_control_query_exec_stats (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

对实例的所有本机编译存储过程或特定的本机编译存储过程启用按查询的统计信息收集。Enables per query statistics collection for all natively compiled stored procedures for the instance, or specific natively compiled stored procedures.

启用统计信息收集时性能下降。Performance decreases when you enable statistics collection. 如果只需要对一个或几个本机编译存储过程进行故障排除,则可仅对这些本机编译存储过程启用统计信息收集。If you only need to troubleshoot one, or a few natively compiled stored procedures, you can enabling statistics collection for just those few natively compiled stored procedures.

若要启用所有本机编译存储过程在过程级别统计信息收集,请参阅sys.sp_xtp_control_proc_exec_stats (TRANSACT-SQL)To enable statistics collection at the procedure level for all natively compiled stored procedures, see sys.sp_xtp_control_proc_exec_stats (Transact-SQL).


sp_xtp_control_query_exec_stats [ [ @new_collection_value = ] collection_value ],  
[ [ @database_id = ] database_id   
[ , [ @xtp_object_id = ] procedure_id ] ,   
[ @old_collection_value] ]  


@new_collection_value =@new_collection_value = value
决定打开 (1) 还是关闭 (0) 过程级统计信息收集。Determines whether procedure-level statistics collection is on (1) or off (0).

@new_collection_value 设置为零SQL ServerSQL Server启动。@new_collection_value is set to zero when SQL ServerSQL Server starts.

@database_id = = database_id, @xtp_object_id = procedure_id@database_id = = database_id, @xtp_object_id = procedure_id
本机编译存储过程的数据库 ID 和对象 ID。The database ID and object ID for the natively compiled stored procedure. 如果为实例启用了统计信息收集 (sys.sp_xtp_control_proc_exec_stats (TRANSACT-SQL)),则收集对本机编译的存储过程的统计信息。If statistics collection is enabled for the instance (sys.sp_xtp_control_proc_exec_stats (Transact-SQL)), statistics on a natively compiled stored procedure are collected. 对实例关闭统计信息收集不会关闭对个别本机编译存储过程的统计信息收集。Turning off statistics collection on the instance does not turn off statistics collection for individual natively compiled stored procedures.

使用sys.databases (TRANSACT-SQL)sys.procedures (Transact SQL)DB_ID (-),或OBJECT_ID (TRANSACT-SQL) 若要获取的数据库和存储的过程的 Id。Use sys.databases (Transact-SQL), sys.procedures (Transact-SQL), DB_ID (Transact-SQL), or OBJECT_ID (Transact-SQL) to get IDs for a database and stored procedure.

@old_collection_value =@old_collection_value = value
返回当前状态。Returns the current status.

返回代码Return Code

0 表示成功。0 for success. 非零表示失败。Nonzero for failure.


要求用户为固定 sysadmin 角色的成员。Requires membership in the fixed sysadmin role.

代码示例Code Sample

以下代码示例展示如何对实例的所有本机编译存储过程启用统计信息收集,然后对特定的本机编译存储过程启用统计信息收集。The following code sample shows how to enable statistics collection for all natively compiled stored procedures for the instance and then for a specific natively compiled stored procedure.

DECLARE @c bit  
EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1;  
EXEC sp_xtp_control_query_exec_stats @old_collection_value=@c output;  
SELECT @c AS 'collection status';  
EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,   
@database_id = 5, @xtp_object_id = 341576255;  
EXEC sp_xtp_control_query_exec_stats @database_id = 5,   
@xtp_object_id = 341576255, @old_collection_value=@c output;  
SELECT @c AS 'collection status';  

请参阅See Also

系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
内存中 OLTP(内存中优化)In-Memory OLTP (In-Memory Optimization)