Hello Geeks and welcome to the Day 66 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 seen how to check the transactions on In-Memory tables using sys.dm_db_xtp_transactions. Today I will cover sys.dm_xtp_transaction_stats. This DMV gives you an idea about how loaded is your server in terms of transactions.
Sys.dm_xtp_transaction_stats is an accumulated result of the stats from the server start up. So you will see all the aggregated information for following columns.
total_counts – Total transactions which executed in In-Memory OLTP engine.
read_only_count – Count of read-only transactions.
total_aborts – Number of transactions which aborted.
validation_failure – Number of times a transaction aborts due to validation failure.
dependencies_failed – Number of times a transaction has aborted as the dependent transaction aborted.
savepoints_created – Number of savepoints created. Every atomic block creates a savepoint.
savepoint_rollbacks – Number of times a rollback occurred on previous savepoint.
log_bytes_written – Number of log bytes written in to In-Memory log records.
log_IO_count – Number of transactions that need log IO. This is only considered with durable tables.
Let us see the output of sys.dm_xtp_transaction_stats.
SELECT total_count, read_only_count, total_aborts, validation_failures, dependencies_failed, savepoint_create, savepoint_rollbacks, log_bytes_written, log_IO_count FROM sys.dm_xtp_transaction_stats
Now the interesting part is Microsoft marks most of other columns as internal use only. But below are few columns which I think are not so internal and which I can make sense from the name. I am covering few which I thought are useful. Rest of them are useful too on case to case basis.
phantom_rows_touched – Number of phantom records that were touched in all transactions.
scans_started – Number of scans started by all transactions.
rows_returned – number of rows returned by all transactions.
rows_touched – number of rows accessed but may not be returned.
row_insert_attempts – Number of attempts to insert rows.
row_update_attempts – Number of attempts to perform an update operation.
row_delete_attempts – Number of delete attempts.
write_conflict – Conflict between transactions while writing a row which leads to aborts.
unique_constraint_violations – Number of unique constraint violations from all transactions.
Lot more internal use only to be explored. I need to build labs to see the use cases of all these columns. 🙂 Will do it someday.
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
