sys.dm_db_xtp_index_stats – Day 77 – One DMV a Day

Hello Geeks and welcome to the Day 77 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.

Today is the day I will be writing my last post specific to DMVs. I have started this series 77 days back with an Index related DMV. I will end the single DMVs with an Index related DMV. In the next three days I will try to write a collection on DMV queries which are helpful in certain scenarios. Today I will be talking about sys.dm_db_xtp_index_stats.

Sys.dm_db_xtp_index_stats has the information of all the indexes on the In-Memory tables. This will provide more information which is mostly internal. The important columns we will be interested will help us in identifying Seeks vs Scans.

SELECT OBJECT_NAME(xis.object_id) AS tableName, AS indName, 
FROM sys.dm_db_xtp_index_stats xis
INNER JOIN sys.indexes i 
ON i.object_id = xis.object_id 
	AND i.index_id = xis.index_id
WHERE OBJECT_NAME(xis.object_id) LIKE 'inMem%'



The two columns which help us are scans_started and rows_returned.

scans_started – As the name suggests this is number of scans started on the index. This also includes the scans for inserts. So this is not the measure of just the selects for direct selects, updates and deletes.
rows_returned – This is the rows returned by the storage engine by all scan operations. The filters at client level are not considered. Another important point is the scans for Insert does not return any rows.

From above columns of sys.dm_db_xtp_index_stats, you can determine few issues. If scans is higher than rows returned then there are more inserts on the table. Which is not a big problem. But what if the scans are mostly due to retry attempts. This can be figured out from other column scans_retries.

If the rows returned is high than scans started then it indicates that scans are scanning a large part of index. This can happen in case of a query to perform a point lookup operation but revert back to full index scan. Mostly in case of hash index where there can be more duplicate indexes.

Now you know how to use most of the DMVs in SQL Server through 2014. Tomorrow I will be writing about few set of DMV queries. So stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.