Trace flag 834 & Columnstore indexes

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

This trace flag(TF) is one of the better known ones and a Bingle search will reveal many hits, for example

https://blogs.technet.microsoft.com/sql_server_isv/2010/11/30/temenos-t24-trace-flag-834-and-when-to-use-it/

TF834 can provide a performance boost

 

or this one by Bob Ward

https://blogs.msdn.microsoft.com/psssql/2009/06/05/sql-server-and-large-pages-explained/

 

So it may be one that DBAs have put it in place by default or on older servers (since upgraded but with the original trace flags retained).

Quick reminder you can check to see which trace flags are running on your server using DBCC TRACESTATUS(-1)

 

Anyway I would like to bring your attention to some nasty side effects if you are using Columnstore Indexes. Here I quote the recently published KB article :-
https://support.microsoft.com/en-us/kb/3210239?sd=rss&spid=18457

In this scenario, you experience one or more of the following performance issues:

A non-yielding scheduler error and associated memory dumps in the SQL Server Error log.

Columnstore queries trigger severe performance issues.

A SQL Server instance triggers access violations when you execute Columnstore queries.

You encounter the following error when you run sp_createstats:

There is insufficient system memory in resource pool ‘default’ to run this query.

 

In conclusion if you are using Columnstore Indexes in your SQL Server database – make sure that TF834 is off.