SQL Server 2012: ColumnStore Part II June 5th, 2013

Vinod Kumar

I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.

Datatype restrictions

In SQL Server 2012, columnstore index creation is supported only for the following data types:

  1. char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
  2. decimal (and numeric)
  3. int, bigint, smallint, and tinyint
  4. float, real
  5. bit
  6. money and smallmoney

New DDL

There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.

CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)

Index hint

We can force an index hint to use the columnstore index in a query if required:

… FROM mytable WITH (INDEX (mycsindex)) …

Query hint

We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:

SELECT DISTINCT (SalesTerritoryKey)
FROM dbo.FactResellerSalesCopy
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

CATALOG VIEWS and DMVs 

There are a number catalog views which will show different values for ColumnStore. I am listing a few here:

sys.indexes:

type : 6

type_desc: NONCLUSTERED COLUMNSTORE

sys.partitions:

data_compression: 3

data_compression_desc: COLUMNSTORE

sys.index_columns:

key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.

sys.dm_db_index_operational_stats:

Columns
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
nonleaf_allocation_count,
nonleaf_page_merge_count,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
Leaf_page_merge_count,
page_compression_attempt_count,
page_compression_success_count – Has a value of 0 for columnstore index.

Showplan Enhancement

Three additional properties have been added to the showplan data for columnstore indexes:

  1. Storage – row or columnstore
  2. EstimatedExecutionMode : row or batch
  3. ActualExecutionMode : row or batch

SSMS Enhancements for Columnstore

I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.

image

The next UI changes is for creating a New Columnstore Index.

image

The third part is around adding columnstore columns as we create the index.

image

Final Words

These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.

Share this article

Tags: , , , , , , , , , ,

This entry was posted on Wednesday, June 5th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply



 

Email
Print