sys.dm_db_xtp_object_stats – Day 69 – One DMV a Day

Hello Geeks and welcome to the Day 69 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 I have covered sys.dm_db_xtp_checkpoint_stats. Today I will be covering sys.dm_db_xtp_object_stats. This DMV is useful when you are troubleshooting high usage object in your In-Memory OLTP engine. Again this is a stats related DMV and the output is aggregated from last restart.

To understand the output of sys.dm_db_xtp_object_stats I will start by first restarting my SQL instance. When I restart the stats are set to the basic values. So let us see the output of first attempt.

SELECT o.name,
	row_insert_attempts,
	row_update_attempts,
	row_delete_attempts,
	write_conflicts,
	unique_constraint_violations
FROM sys.dm_db_xtp_object_stats xos
INNER JOIN sys.objects o
ON xos.object_id = o.object_id
WHERE o.name = 'inMem_tbl'

From the above output you can see there were 10000 insert attempts and 25000 update attempts already. This is because the whole table is rebuilt in the memory for durable tables. If this has been a non-durable table the counts would be zeros. Now let us run an update from two sessions so there will be a conflict.

UPDATE inMem_tbl
SET col2 = col2
WHERE col1 > 5000

One of them will fail with below error.

Let us see the output again from sys.dm_db_xtp_object_stats.

From the output you can see that the update attempts increased by only 5000 which is one update statement. As one of them failed the update attempts were not recorded. But the conflict count has increased to 1. This is how the counts and other columns affect in sys.dm_db_xtp_object_stats.

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