Skip to content

Commit 98ab5b0

Browse files
PMEds28PMEds28
authored
Merge pull request #16067 from pmasl/patch-852
Update monitoring-performance-of-natively-compiled-stored-procedures.md
2 parents 876a915 + 62ba3ed commit 98ab5b0

1 file changed

Lines changed: 35 additions & 59 deletions

File tree

‎docs/relational-databases/in-memory-oltp/monitoring-performance-of-natively-compiled-stored-procedures.md‎

Lines changed: 35 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -21,46 +21,45 @@ monikerRange: "=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversio
2121
## Using Extended Events
2222
Use the **sp_statement_completed** extended event to trace execution of a query. Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. The extended event is raised after the execution of each query. The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. A natively compiled stored procedure that uses a lot of CPU time may have performance problems.
2323

24-
**line_number**, along with the **object_id** in the extended event can be used to investigate the query. The following query can be used to retrieve the procedure definition. The line number can be used to identify the query within the definition:
24+
The **line_number**, along with the **object_id** in the extended event can be used to investigate the query. The following query can be used to retrieve the procedure definition. The line number can be used to identify the query within the definition:
2525

2626
```sql
2727
SELECT [definition]
28-
from sys.sql_modules
29-
where object_id=object_id;
28+
FROM sys.sql_modules
29+
WHERE object_id=object_id;
3030
```
31-
32-
31+
3332
## Using Data Management Views and Query Store
34-
[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and [!INCLUDE[ssSDS](../../includes/sssds-md.md)] support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Collecting execution statistics is not enabled by default due to performance impact.
33+
[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and [!INCLUDE[ssSDSfull](../../includes/sssdsfull-md.md)] support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Collecting execution statistics is not enabled by default due to performance impact.
3534

3635
Execution statistics are reflected in the system views [sys.dm_exec_procedure_stats](../../relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql.md) and [sys.dm_exec_query_stats](../../relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql.md), as well as in [Query Store](../../relational-databases/performance/monitoring-performance-by-using-the-query-store.md).
3736

3837
## Procedure-Level Execution Statistics
3938

4039
**[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using [sys.sp_xtp_control_proc_exec_stats (Transact-SQL)](../../relational-databases/system-stored-procedures/sys-sp-xtp-control-proc-exec-stats-transact-sql.md). The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:
40+
4141
```sql
4242
EXEC sys.sp_xtp_control_proc_exec_stats 1
4343
```
4444

45-
**[!INCLUDE[ssSDSFull](../../includes/sssdsfull-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the [database-scoped configuration](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md) option `XTP_PROCEDURE_EXECUTION_STATISTICS`. The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:
45+
**[!INCLUDE[ssSDSFull](../../includes/sssdsfull-md.md)]** and **[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the [database-scoped configuration](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md) option `XTP_PROCEDURE_EXECUTION_STATISTICS`. The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:
46+
4647
```sql
47-
ALTER DATABASE
48-
SCOPED CONFIGURATION
49-
SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;
48+
ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;
5049
```
5150

5251
## Query-Level Execution Statistics
5352

5453
**[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the query-level using [sys.sp_xtp_control_query_exec_stats (Transact-SQL)](../../relational-databases/system-stored-procedures/sys-sp-xtp-control-query-exec-stats-transact-sql.md). The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:
54+
5555
```sql
5656
EXEC sys.sp_xtp_control_query_exec_stats 1
5757
```
5858

59-
**[!INCLUDE[ssSDSFull](../../includes/sssdsfull-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the [database-scoped configuration](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md) option `XTP_QUERY_EXECUTION_STATISTICS`. The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:
59+
**[!INCLUDE[ssSDSFull](../../includes/sssdsfull-md.md)]** and **[!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]**: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the [database-scoped configuration](../../t-sql/statements/alter-database-scoped-configuration-transact-sql.md) option `XTP_QUERY_EXECUTION_STATISTICS`. The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:
60+
6061
```sql
61-
ALTER DATABASE
62-
SCOPED CONFIGURATION
63-
SET XTP_QUERY_EXECUTION_STATISTICS = ON;
62+
ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;
6463
```
6564

6665
## Sample Queries
@@ -71,60 +70,37 @@ ALTER DATABASE
7170
The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:
7271

7372
```sql
74-
SELECT
75-
object_id,
76-
object_name(object_id) as 'object name',
77-
cached_time,
78-
last_execution_time, execution_count,
79-
total_worker_time, last_worker_time,
80-
min_worker_time, max_worker_time,
81-
total_elapsed_time, last_elapsed_time,
82-
min_elapsed_time, max_elapsed_time
83-
from
84-
sys.dm_exec_procedure_stats
85-
where
86-
database_id = db_id()
87-
and
88-
object_id in
89-
(
90-
SELECT object_id
91-
from sys.sql_modules
92-
where uses_native_compilation=1
93-
)
94-
order by
95-
total_worker_time desc;
73+
SELECT object_id, object_name(object_id) AS 'object name',
74+
cached_time, last_execution_time, execution_count,
75+
total_worker_time, last_worker_time,
76+
min_worker_time, max_worker_time,
77+
total_elapsed_time, last_elapsed_time,
78+
min_elapsed_time, max_elapsed_time
79+
FROM sys.dm_exec_procedure_stats
80+
WHERE database_id = DB_ID()
81+
AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
82+
ORDER BY total_worker_time desc;
9683
```
9784

9885
The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:
9986

10087
```sql
101-
SELECT
102-
st.objectid,
103-
object_name(st.objectid) as 'object name',
88+
SELECT st.objectid,
89+
OBJECT_NAME(st.objectid) AS 'object name',
10490
SUBSTRING(
10591
st.text,
10692
(qs.statement_start_offset/2) + 1,
10793
((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
108-
) as 'query text',
109-
qs.creation_time,
110-
qs.last_execution_time, qs.execution_count,
111-
qs.total_worker_time, qs.last_worker_time,
112-
qs.min_worker_time, qs.max_worker_time,
113-
qs.total_elapsed_time, qs.last_elapsed_time,
114-
qs.min_elapsed_time, qs.max_elapsed_time
115-
FROM
116-
sys.dm_exec_query_stats qs
117-
cross apply sys.dm_exec_sql_text(sql_handle) st
118-
WHERE
119-
st.dbid = db_id()
120-
and
121-
st.objectid in
122-
(SELECT object_id
123-
from sys.sql_modules
124-
where uses_native_compilation=1
125-
)
126-
ORDER BY
127-
qs.total_worker_time desc;
94+
) AS 'query text',
95+
qs.creation_time, qs.last_execution_time, qs.execution_count,
96+
qs.total_worker_time, qs.last_worker_time, qs.min_worker_time,
97+
qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
98+
qs.min_elapsed_time, qs.max_elapsed_time
99+
FROM sys.dm_exec_query_stats qs
100+
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
101+
WHERE database_id = DB_ID()
102+
AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
103+
ORDER BY total_worker_time desc;
128104
```
129105

130106
## Query Execution Plans

0 commit comments

Comments
 (0)