Skip to content

sys-sp-xtp-control-query-exec-stats-transact-sql.md

Latest commit

 

History

History
96 lines (66 loc) · 3.76 KB

sys-sp-xtp-control-query-exec-stats-transact-sql.md

File metadata and controls

96 lines (66 loc) · 3.76 KB
title sys.sp_xtp_control_query_exec_stats (Transact-SQL)
description Enables per query statistics collection for all or specific natively compiled stored procedures on an instance.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sys.sp_xtp_control_query_exec_stats_TSQL
sys.sp_xtp_control_query_exec_stats
helpviewer_keywords
sys.sp_xtp_control_query_exec_stats
dev_langs
TSQL

sys.sp_xtp_control_query_exec_stats (Transact-SQL)

[!INCLUDE sqlserver]

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 enable 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.

Syntax

sys.sp_xtp_control_query_exec_stats
    [ [ @new_collection_value = ] collection_value ]
    [ , [ @database_id = ] database_id ]
    [ , [ @xtp_object_id = ] procedure_id ]
    , [ @old_collection_value = ] old_collection_value OUTPUT
[ ; ]

Arguments

[ @new_collection_value = ] collection_value

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

@new_collection_value is set to 0 when [!INCLUDE ssNoVersion] starts.

[ @database_id = ] database_id, [ @xtp_object_id = ] procedure_id

The database ID and object ID (data type int) for the natively compiled stored procedure. If statistics collection is enabled for the instance (sys.sp_xtp_control_proc_exec_stats), statistics on a natively compiled stored procedure are collected. Turning off statistics collection on the instance doesn't turn off statistics collection for individual natively compiled stored procedures.

Use sys.databases, sys.procedures, DB_ID, or OBJECT_ID to get IDs for a database and stored procedure.

[ @old_collection_value = ] old_collection_value OUTPUT

Returns the current status. @old_collection_value is bit.

Return code values

0 for success. Nonzero for failure.

Permissions

Requires membership in the fixed sysadmin role.

Examples

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 AS BIT;

EXECUTE sys.sp_xtp_control_query_exec_stats @new_collection_value = 1;

EXECUTE sys.sp_xtp_control_query_exec_stats
    @old_collection_value = @c OUTPUT;

SELECT @c AS 'collection status';

EXECUTE sys.sp_xtp_control_query_exec_stats
    @new_collection_value = 1,
    @database_id = 5,
    @xtp_object_id = 41576255;

EXECUTE sys.sp_xtp_control_query_exec_stats
    @database_id = 5,
    @xtp_object_id = 41576255,
    @old_collection_value = @c OUTPUT;

SELECT @c AS 'collection status';

Related content