Edit

Performance may be slow when columnstore indexes are used with large page memory

  • Article
  • 2 minutes to read

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 operation 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 doesn't 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