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

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

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.