SQL SERVER – Columnstore Index and sys.dm_db_index_usage_stats

As you know I have been writing on Columnstore Index for quite a while. My friend suggested that I should write on DMV script which I have prepared related to Columnstore when I was writing our SQL Server Questions and Answers book. I had written few scripts related to SQL Server columnstore Index.

The first script which I had written was with DMV – sys.column_store_index_stats. This DMV was displaying the statistics of the columnstore indexes. When I attempted to run it on SQL Server 2012 RTM it gave me error suggesting that this DMV does not exist.

Here is the script which I ran:

SELECT *
FROM sys.column_store_index_stats;

It generated following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘column_store_index_stats’.

I was pretty confident that this DMV was available when I had written the scripts. The next reaction was to type ‘sys.’ only in SSMS and wait for intelisense to popup DMV list. I scrolled down and noticed that above said DMV did not exist there as well.

Now, this is not a bug or missing feature. This was indeed something can happen because the version which I was practicing was early CTP version. If you go to the page of the DMV here, it clearly stats notice on the top of the page.

This documentation is for preview only and is subject to change in later releases.

Now, this was not alarming but my next thought was if this DMV is not there where can I find the information which this DMV was providing. Well, while I was thinking about this, I noticed that my another friend Balmukund Lakhani was online on personal messenger. Well, Balmukund is “Know All” kid. I have yet to find a situation where I have not got my answers from him. I immediately pinged him and asked the question regarding where can I find information about ‘column_store_index_stats’. His answer was very abrupt but enlightening for sure. Here is our conversation:

Pinal: Where can I find information of column_store_index_stats?
Balmukund: Assume you have never worked with CTP before and now try to find the information which you are trying to find.

Honestly, it was a fantastic response from him. I was confused as I have played extensively with CTP versions of SQL Server 2012. Now his response gives me big hint. I should have not looked for DMV but rather should have focused on what I wanted to do. I wanted to retrieve the statistics related to the index. In SQL Server 2008/R2, I was able to retrieve the statistics of the index from the DMV – sys.dm_db_index_usage_stats. I used the same DMV on SQL Server 2012 and it did retrieve the necessary information for me.

Here is the updated script which gave me all the necessary information I was looking for. Matter of the fact, if I have used my earlier SQL Server 2008 R2 script this would have just worked fine.

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc, i.name,
user_seeks, user_scans,
user_lookups, user_updates,*
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t ON t.OBJECT_ID = i.OBJECT_ID
GO

Let us see the resultset of above query.

You will notice that column Type_desc describes the type of the index. You can additionally write WHERE condition on the column and only retrieve only selected type of Index.

Reference: Pinal Dave (https://blog.sqlauthority.com)