Interoperability of Columnstore indexes with large page memory model in SQL Server

Applies to: SQL Server 2016 StandardSQL Server 2016 EnterpriseSQL Server 2014 Standard

Symptoms


Consider the following scenario:
  • In an instance of SQL Server, you use trace flag 834 as a startup flag. You do this to enable large page allocations by the SQL Server memory manager in order to improve performance of the 64-bit instance.
  • You use the Columnstore Index feature.
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

Resolution


To resolve this issue, remove trace flag 834 (-T834) from SQL Server startup parameters on SQL Server instances that use Columnstore indexes. In these environments, Microsoft does not recommend using a large page memory model and encourages customers to revert to a conventional or lock pages memory model.
Note: Starting with SQL Server 2019, Trace flag (TF) 876 is available to enable the large page memory model for columnstore. See the Trace Flags (Transact-SQL) page for additional information.