As a SQL Server enthusiast, I have been diving deep into the world of Columnstore Indexes. Recently, a friend suggested that I write about a DMV script related to Columnstore Indexes that I had prepared while working on our SQL Server Questions and Answers book. This script was designed to display the statistics of the columnstore indexes. However, when I tried running it on SQL Server 2012 RTM, I encountered an error stating that the DMV ‘sys.column_store_index_stats’ does not exist.

Initially, I was puzzled because I was confident that this DMV was available when I had written the script. To investigate further, I opened SQL Server Management Studio (SSMS) and typed ‘sys.’ to see the list of available DMVs. To my surprise, the ‘column_store_index_stats’ DMV was nowhere to be found. It was then that I realized that this was not a bug or missing feature, but rather a result of the version I was using, which was an early CTP version.

Upon visiting the documentation page for the DMV, I found a notice at the top stating that the documentation was for preview only and subject to change in later releases. While this was not alarming, it raised the question of where I could find the information that this DMV was supposed to provide.

Fortunately, I had a knowledgeable friend, Balmukund Lakhani, who happened to be online. Balmukund is known for his expertise in SQL Server, so I immediately reached out to him for guidance. I asked him where I could find information about ‘column_store_index_stats’, and his response was both abrupt and enlightening.

Balmukund’s advice was simple yet profound. He told me to assume that I had never worked with a CTP version before and to approach the problem as if I were starting from scratch. This made me realize that instead of focusing on the specific DMV, I should have been thinking about what I wanted to achieve – retrieving statistics related to the index.

In SQL Server 2008/R2, I had successfully retrieved index statistics using the DMV ‘sys.dm_db_index_usage_stats’. Following Balmukund’s advice, I decided to try the same DMV on SQL Server 2012, and to my delight, it provided me with all the necessary information. In fact, if I had used my earlier SQL Server 2008 R2 script, it would have worked just fine.

Here is the updated script that gave me the desired results:

SELECT DB_NAME(Database_ID) AS DBName,
       SCHEMA_NAME(schema_id) AS SchemaName,
       OBJECT_NAME(ius.OBJECT_ID) AS 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;

By executing the above query, you will obtain a result set that includes the ‘Type_desc’ column, which describes the type of the index. You can further refine the results by adding a WHERE condition on this column to retrieve only the selected type of index.

Exploring SQL Server DMVs can be a fascinating journey, and it’s important to adapt to changes and updates in each version. Remember, sometimes the solution lies not in the specific DMV, but in understanding the underlying goal and finding alternative ways to achieve it.

Stay tuned for more SQL Server insights and tips!