Columnstore Indexes: Finally Awesome in SQL 2016

Columnstore indexes are amazing for optimizing the performance of large rowcount queries that would otherwise look like a SCAN operation in an execution plan. Introduced in SQL Server 2012, they render the table read-only, however, and needed to be dropped (not even disabled) in order to insert/update/delete rows.

If you tried to update a table with a nonclustered columnstore index in SQL 2012 or SQL 2014:

Msg 35330, Level 15, State 1, Line 2
UPDATE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, and then rebuilding the columnstore index after UPDATE has completed.

In SQL 2014, you can now make a clustered columnstore index, but it has only narrow, specific uses because clustered columnstore indexes don't support traditional (B-tree) nonclustered indexes, a primary key, foreign keys, etc.

All those limitations above are going away in SQL 2016. Right now: SQL 2016 will release on June 1 2016. Release candidates are available for download but aren’t for production use.

SQL 2012 - Nonclustered columnstore indexes can be created, but aren’t updateable. 

SQL 2014 - Nonclustered columnstore indexes can be created, but still aren’t updateable. Clustered columnstore indexes can now be created, are updateable.

SQL 2016 - Nonclustered and clustered columnstore indexes can be created, and are updateable. Plus, you can mix clustered columnstore with traditional B-tree indexes. (Lots of other features too!)

More about these features as Columnstore indexes evolve here: https://msdn.microsoft.com/en-us/library/dn934994%28v=sql.130%29.aspx

Want to benefit from columnstore indexes, but not using SQL 2016 yet?

You have three options.

1) Disable/rebuild columnstore nonclustered indexes when you do updates to the table. Not a big deal for nightly-build data warehouses, but painful for frequently-updating tables. Note that the ONLINE feature to create traditional indexes isn't available here.

DROP INDEX [IDX_CS_Table_Covering] ON [dbo].[Table]
GO
<do updates/inserts>
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table] ( Column1, Column2... )
GO

(When you move to SQL 2016, these drop/create steps won’t be necessary any more.)

2) SQL 2014 only. Drop all existing clustered/nonclustered indexes and foreign keys on the table, change the table to have a single clustered columnstore index. This requires performance testing for smaller-scope queries, but should provide the big performance gains you’re looking for, at the cost of dropping the primary key and related foreign keys. This is suitable for narrow use only in reporting database designs and not in normalized environments.

<drop all existing indexes on [dbo].[Table], including the primary key and linked foreign keys>
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table]
GO

3) SQL 2014 only. Create a copy of the table that implements 2) above, so you’ll have a table with traditional indexes plus an exact copy of the table but with only a clustered columnstore index. You would then need to point big report queries at dbo.Table_CS and other queries (that use your traditional nonclustered indexes) at dbo.Table. And we would need to modify all insert/update/delete operations in the ETL on dbo.Table in order to replicate any row operations into dbo.Table_CS. This is painful, I understand, but should provide the big performance gains you're looking for. (It would be totally unnecessary to design two tables and redirect some reports in this way if you intend to move to SQL 2016 in the near term, so this step is not recommended.)

That's it!

Need proof that columnstore indexes are awesome and will improve the performance of your large queries? Get used to loving columnstore scans! 

SSRS 2016 then SQL Jeopardy! tonight at BRSSUG!

See you tonight for our special SQL Jeopardy! competition where I will be your Trebek and three lucky user group participants will be our contestants!

Also a "special appearance" by our BRSSUG founder and Microsoft architect Patrick Leblanc, who will do a lightning round presentation on What's New in SSRS 2016.

See you there tonight!
brssug.org

SQL Server PerfMon Counters Not Listed in dm_os_performance_counters

Had an issue with a new Azure VM install of SQL Server, but have had this issue in other, older places as well. SQL Server's performance counters in dm_os_performance_counters and/or perfmon have gone missing. How to get them back?

You might see a small subset of all performance counters here in the DMV view. (There should be 1335 rows in this DMV for SQL Server 2014 RTM, 1245 for SQL 2012 SP2. More are added regularly with major releases.) In the example I saw this morning, only 51 rows were returned, and only for the XTP series of objects (XTP Transaction Log, XTP Cursors, etc).

Strangely, I had now found this problem on brand-new Azure VM's running SQL Server, specifically in the pre-built SQL 2014 Availability Group resource group, which can be deployed easily from the new Azure portal.

The issue can be resolved with a pair of PowerShell commands which re-register performance counter names and explanation text to the SQL Server service in the Windows registry.

In PowerShell, modify the below for your service.

• In the first line, change the driver name parameter to match the SQL Server instance name, MSSQLSERVER is the name for the default instance. To do the same for a specific instance, it would be MSSQL$InstanceName. 
• In the second line, replace my path below with the path for your instance.

unlodctr MSSQLSERVER

lodctr "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"

A restart of the affected service, in this case the SQL Server service, is necessary for the changes to take effect.