12

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.

Improve this question
4
  • support.microsoft.com/kb/920093 -- which O/S are you running, and what kind of workload is this server handling?
    – Jon Seigel
    Commented Aug 18, 2011 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
    Commented Aug 19, 2011 at 5:10
  • Not a complete answer I know but quite a useful blog post here
    – georgeb
    Commented Aug 19, 2011 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
    Commented Aug 19, 2011 at 13:03

2 Answers 2

Reset to default
4
+50

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!

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

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.

Luck.

Improve this answer
1
  • +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
    Commented Sep 1, 2011 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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