Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.sp_xtp_control_query_exec_stats (Transact-SQL)


Updated: October 13, 2015

Applies To: SQL Server

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.

To enable statistics collection at the procedure level for all natively compiled stored procedures, see sys.sp_xtp_control_proc_exec_stats (Transact-SQL).

Applies to: SQL Server (SQL Server 2014 through current version).

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 = value

Determines whether procedure-level statistics collection is on (1) or off (0).

@new_collection_value is set to zero when SQL Server starts.

@database_id = = database_id, @xtp_object_id =  procedure_id

The database ID and object ID for the natively compiled stored procedure. 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.

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 = value

Returns the current status.

0 for success. Nonzero for failure.

Requires membership in the fixed sysadmin role.

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';

Community Additions

© 2016 Microsoft