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, i.name AS indName, scans_started, rows_returned 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,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
