Hi Friends,
This is my 28th blog on SQL Server Trace Flag 834 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
Trace flag 834 is one of the trace flag that can be used to alter one of the default working of SQL Server. By default, SQL Server decides the use of large page allocations support from windows based on below conditions:
1- SQL Server edition should be Enterprise
2- And System should have >= 8 GB RAM
3- And “Locked Pages in memory” should be enabled for SQL Server service account
Based on all above conditions, SQL Server uses large page allocation but not for all memory allocated to buffer pool. Here if you want to allocate all the buffer pool memory through windows large page allocation then you will use trace flag 834. Large page size depends on hardware and size can be varying from 2 Mb to 16 MB. Keep in mind that trace flag 834 can be enabled on 64 bit version of SQL Server. Under this trace flag all the buffer pool memory will be allocated during startup. This may increase the performance of queries. While using trace flag 834, keep in mind that SQL Server will not start if large pages can’t be allocated due to fragmented memory.
References: Click Here.
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
