sql server trace flag 834

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.


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


About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.