Performance may be slow when columnstore indexes are used with large page memory
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
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
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
There is insufficient system memory in resource pool 'default' to run this query
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
lock pages memory model.
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).