Hello Geeks and welcome to the Day 65 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Yesterday we have started with the In-Memory OLTP related DMVs. We have seen how to check the memory consumers in the Hekaton engine using sys.dm_db_xtp_memory_consumers. Today I will be covering sys.dm_db_xtp_transactions. This DMV gives the transaction details on an In-Memory table.
Sys.dm_db_xtp_transactions is different from sys.dm_tran_active_transactions. There can be natively compiled stored procedures which do not have an entry in the later DMV. All other transactions can be linked to a transaction id in the later DMV. Most of the columns returned are for internal use by Microsoft.
Sys.dm_db_xtp_transactions is useful when you are extensively using natively compiled stored procedures. Let us see the important columns from this DMV. In In-Memory OLTP there is no concept of locking and blocking. When a transaction is run it starts and proceed. When committing if the changes are no longer valid it fails. So few states of a transaction are no longer valid.
xtp_transaction_id – Id used for this transaction in the XTP transaction manager.
transaction_id – Can be used to link with sys.dm_tran_active_transactions. Value is 0 for XTP-only transactions.
session_id – Session which owns this transaction.
begin_tsn – Begin transaction sequence number of the transaction.
end_tsn – End transaction sequence number of the transaction.
state_desc – Status of the transaction. Values can be ACTIVE, COMMITTED, ABORTED, VALIDATING.
result_desc – Provides the outcome of the transaction. Possible values are IN PROGRESS, SUCCESS, ERROR, COMMIT DEPENDENCY, VALIDATION FAILED (RR), VALIDATION FAILED (SR), ROLLBACK
Let me run a simple select on the table we created and see the sample output from sys.dm_db_xtp_transactions.
SELECT xtp_transaction_id, transaction_id, session_id, begin_tsn, end_tsn, state_desc, result_desc FROM sys.dm_db_xtp_transactions
Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
