Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any things to watch out for, tips, pitfalls?

The server is a Windows 2008 64 bit, 128 GB RAM, 4 CPU 8 core hyperthreded (total 64 cores) SQL2005 server. I am looking to tune the server to better use its specs than just using the default SQL installation that is currently done on it. Any extra tips will be welcomed.

  • support.microsoft.com/kb/920093 -- which O/S are you running, and what kind of workload is this server handling? – Jon Seigel Aug 18 '11 at 17:03
  • @Jon Seigel I have edited for OS (Win2K8). The workload is oltp. I already have the KB link, but I have not used this feature yet. – StanleyJohns Aug 19 '11 at 5:10
  • Not a complete answer I know but quite a useful blog post here – georgeb Aug 19 '11 at 11:12
  • @george_dba, yes I had read this blog before. What I am concerned with is things like using the lock pages in memory. There has been some concerns about using this feature. – StanleyJohns Aug 19 '11 at 13:03

Thomas Kejser from Microsoft (blog) regularly presents that the 834 trace flag is the only go-faster trace flag for SQL Server. He would caveat that, I'm sure, with plenty of testing!

  • 3
    Here's an article from Thomas which mentiones (briefly) TF834 - sqlug.se/Portals/0/Nyheter/… – Mark Storey-Smith Aug 31 '11 at 22:07
  • @Mark Storey-Smith good catch :) – garik Aug 31 '11 at 23:00
  • @Mark Storey-Smith thanks for the PDF. It has some great pointers for tuning OLTP systems. – StanleyJohns Sep 1 '11 at 13:40

Difficult question. What I have opened for myself and, I hope, you will find out something new:

Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server. more

On my opinion, it is a good enough (and ONLY) documents: Troubleshooting Performance Problems in SQL Server 2005 and for SQL Server 2008: Troubleshooting Performance Problems in SQL Server 2008. Two words about Denali and what is new: SQL Server Memory manager changes in Denali.


  • +1, The SQL technical article has very practical steps. I keep one with me for quick reference, and also for the ready to use TSQL queries that it has. – StanleyJohns Sep 1 '11 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.