SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are not only useful to indicate the overall health of the SQL Server, but also of the databases hosted by the given SQL Server instance. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

Traditional SQL Server indexes are “row-centric”, i.e. they are focused on grouping and storing of each record. Most applications process only one record at a time, and therefore, this mechanism works. However, some kinds of queries need to process/read all records of a particular set of columns. Columnstore indexes, introduced with Microsoft SQL Server 2012 group and store data for each column and then join with rest of the columns to complete the index. The columnstore index can significantly improve the query execution times for such queries.

An introduction to the Columnstore indexes is available in the following posts from Vinod Kumar, a.k.a. “Extreme Experts” (B|T) and Pinal Dave, a.k.a. “SQLAuthority” (B|T):

• Columnstore Characteristics
• Fundamentals of Columnstore index
• Ignoring columnstore index in specific queries

The resources above offer a great start to columnstore index usage. The following two catalog views available for columnstore indexes.

sys.column_store_segments

Introduced In: SQL Server 2012

Purpose: Contains a record for each column in the columnstore index.

Permissions: VIEW DEFINITION permission to the underlying table. For the columns has_nulls, base_id, magnitude, min_data_id, max_data_id, and null_value to be populated, the user also require the SELECT permissions.

BOL Link: http://msdn.microsoft.com/en-us/library/gg509105.aspx

sys.column_store_dictionaries

Introduced In: SQL Server 2012

Purpose: Contains a record for each column in a memory-optimized columnstore index.

Permissions: VIEW DEFINITION permission to the underlying table. For the columns last_id, entry_count and data_ptr to be populated, the user also require the SELECT permissions.

BOL Link: http://msdn.microsoft.com/en-us/library/gg492082.aspx

Until we meet next time,

Be courteous. Drive responsibly.