Extreme Transaction Processing (XTP)
The SQL Server XTP Databases performance object provides In-Memory OLTP database-specific counters. In other words, the SQL Server XTP Transactions performance object contains counters related to transactions involving In-Memory OLTP in SQL Server. The following is good resources from Microsoft's docs
XTP Databases -- https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-xtp-databases?view=sql-server-ver15
XTP Transactions -- https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-xtp-transactions?view=sql-server-ver15
One excellent blog tackles this topic is by Klaus Aschenbrenner -- https://www.sqlpassion.at/archive/2013/08/12/extreme-transaction-processing-xtp-hekaton-the-solution-to-everything. In this article, he points out 3 main pillars of Extreme Transaction Processing in SQL Server :
Dynamic view for database with an memory-optimized table, the system T-SQL normally starts with sys.dm_db_xtp_....., where dm stands for Dynamic View, db stands for DataBse, and xtp stands for Extreme Transaction Processing of memory-optimized table.
Likewise, the system T-SQL starts with sys.sp_xtp_....., where sp is natively complied Stored Procedure, and xtp stands for Extreme Transaction Processing of memory-optimized table.
Why use Memory-optimized tables?
Memory-optimized tables are well-suited for specific OLTP scenarios: high data ingestion rate; high volume, high performance data reads; complex business logic in stored procedures; real-time data access; session state management; applications relying heavily on temporary tables, table variables, and table-valued parameters; and ETL operations.
Besides implementing memory-optimized tables to improve an application’s performance, you can also consider the following techniques to optimize performance even more: natively compiled stored procedures, the addition of indexes to the memory-optimized tables, the use of a readable secondary in an Always On configuration to which you can offload analytics workloads, non-durable tables, or delayed durability for transactions.
Why use natively compiled stored procedure?
Natively compiled stored procedures typically execute faster and are best suited for applications requiring high performance, queries that execute frequently, and tasks that must perform extremely fast. You experience better performance gains over an interpreted stored procedure when a natively compiled stored procedure must process many rows of data and apply complex logic.
Use the system stored procedures sys.sp_xtp_control_proc_exec_stats and sys.sp_xtp_control_query_exec_stats to enable or disable the collection of execution statistics for natively compiled stored procedures at the procedure level or querylevel, respectively. After enabling statistics collection, use thesys.dm_exec_procedure_stats and sys.dm_exec_query_stats DMVs to review thestatistics.
The goal of using memory-optimized tables is to execute processes as quickly as possible. Consequently, you could be surprised that some statistics, such as worker_time and elapsed_time, do not get collected by DMVs such as sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. In fact, these DMVs include no information about natively compiled stored procedures. Instead, you need to specifically enable the collection of execution statistics by using one of the following system stored procedures: sys.sp_xtp_control_proc_exec_stats & sys.sp_xtp_control_query_exec_stats.
sys.sp_xtp_control_proc_exec_stats Use the sys.sp_xtp_control_proc_exec_stats system stored procedure to enable and disable procedure-level statistics collection on your SQL Server instance. When SQL Server or a database starts, statistics collection is automatically disabled. Note that you must be a member of the sysadmin role to execute this stored procedure.
sys.sp_xtp_control_query_exec_stats using the sys.sp_xtp_control_query_exec_stats system procedure enables and disables query-level statistics collection. You can even use it to enable statistics collection for a specific natively compiled stored procedure, but it must have been executed at least once before you enable statistics collection. When SQL Server starts, query-level statistics collection is automatically disabled. Note that disabling statistics collection at the procedure level does not disable any statistics collection that you have configured at the query level. As with the previous system stored procedure, you must be a member of the sysadmin role to execute sys.sp_xtp_control_query_exec_stats.
Example 1:
Answer 1:
Example 2:
Answer 2:
