Sometimes you may stumble upon a slow running query in SQL server and when you…
This trace flag(TF) is one of the better known ones and a Bingle search will reveal many hits, for example
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.