SQL Server 2016 brings about several important enhancements for columnstore indexes. These enhancements aim to improve application performance and scalability. In this blog post, we will explore these enhancements, starting with the new/altered system views for disk-based tables.

System Views for Columnstore Indexes

SQL Server 2016 introduces new/altered system views for columnstore indexes. These views provide valuable information about the internal structures and operational behavior of columnstore indexes. Here are some of the key system views:

• sys.column_store_row_groups: Provides information on the columnstore row groups in a database.
• sys.dm_column_store_object_pool: Provides information on memory-pool usage for columnstore-index structures.
• sys.dm_db_column_store_row_group_operational_stats: Returns information on row-group usage statistics and locking for reads/writes.
• sys.dm_db_column_store_row_group_physical_stats: Extends the sys.column_store_row_groups view and provides information on the extent of fragmentation of columnstore indexes.
• sys.internal_partitions: Returns the internal supporting rowset structures within each columnstore index.
• sys.dm_db_index_operational_stats: Returns usage statistics and I/O-related activity for each partition of a table or index.
• sys.dm_db_index_physical_stats: Returns information on the size and fragmentation of indexes and heaps in a table, database, or instance.

Key Concepts

Before diving into the details of these system views, let’s briefly review some key concepts related to columnstore indexes:

• Columnstore: A structure introduced in SQL Server 2012 that stores data in a columnar format, providing significant performance benefits for data-warehouse-type queries.
• Rowstore: The traditional b-tree/heap index structure.
• Rowgroup: A group of rows that are compressed together into a columnstore.
• Segment: Data from a single column compressed individually and combined with the segments of other columns in the index as part of a rowgroup.
• Dictionary: An auxiliary structure used to encode the data in a column ahead of its compression into a column segment.
• Deltastore: A b-tree structure for storing data that is not yet large enough to compress into a columnstore.
• Delete Bitmap: A b-tree structure used for storing the row IDs of deleted rows in the columnstore index.
• Tuple Mover: A background process that periodically compresses rowgroups into columnstore format.

Using the System Views

Now that we have an understanding of the key concepts, let’s explore how we can use the system views to analyze the internals and operational behavior of columnstore indexes.

For example, the sys.column_store_row_groups view provides information on the columnstore row groups in a database. This can help us understand the distribution of rows across row groups and identify any fragmentation issues.

The sys.dm_column_store_object_pool view provides information on memory-pool usage for columnstore-index structures. This can help us monitor the memory utilization of column segments and associated structures.

The sys.dm_db_column_store_row_group_operational_stats view returns information on row-group usage statistics and locking for reads/writes. This can help us analyze the performance and concurrency of queries against columnstore indexes.

The sys.dm_db_column_store_row_group_physical_stats view extends the sys.column_store_row_groups view and provides information on the extent of fragmentation of columnstore indexes. This can help us identify fragmented row groups and take appropriate maintenance actions.

The sys.internal_partitions view returns the internal supporting rowset structures within each columnstore index. This can help us understand the underlying structures and their roles in the columnstore index.

The sys.dm_db_index_operational_stats view returns usage statistics and I/O-related activity for each partition of a table or index. This can help us monitor the I/O and locking activity of columnstore indexes.

The sys.dm_db_index_physical_stats view returns information on the size and fragmentation of indexes and heaps. This can help us identify fragmentation issues and optimize the storage of columnstore indexes.

Conclusion

In this blog post, we have explored the new/altered system views introduced in SQL Server 2016 for analyzing the internals and operational behavior of columnstore indexes. These system views provide valuable insights into the performance and scalability of columnstore indexes. By leveraging these views, database administrators and developers can optimize their applications and achieve improved performance and scalability.

In subsequent blog posts, we will continue to explore other enhancements related to columnstore indexes in SQL Server 2016.