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.
- 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
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.