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

This article helps you resolve the performance issue when you use Columnstore Index feature and large page memory model in SQL Server.

Original product version:   SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019
Original KB number:   3210239

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 DBCC TRACEON - Trace Flags (Transact-SQL).

More information