Trace flag 834

[Database Administrator]

Does anyone have an experience using this trace flag in a production
environment? When you would use it and how to tell if it has a positive
effect? I gather that it might be a good option if you are not IO bound but
have a CPU bottleneck.

http://blogs.msdn.com/mtcdata/archiv...vldb-tips.aspx

http://msdn2.microsoft.com/en-us/library/aa366720.aspx

http://support.microsoft.com/kb/920093

Trace flag 834: Use Microsoft Windows large-page allocations for the buffer
pool
Trace flag 834 causes SQL Server 2005 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 2005. 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 2005.

For more information about large-page support in Windows, visit the
following Microsoft Developer Network (MSDN) Web site:

[Database Administrator]

> effect? I gather that it might be a good option if you are not IO bound
> but have a CPU bottleneck.

I am not using this trace flag. I can't think of a system I've ever seen
that was not I/O bound (even those using EMC SANs).

[Database Administrator]

I mean when there is more than enough memory for the bufferpool so we are
not waiting on reads. For example, high signal wait time vs. resource wait
time. I am just guessing though based on this comment:
Trace flag 834 improves performance
by increasing the efficiency of the translation look-aside buffer (TLB) in
the CPU.



"Aaron Bertrand [SQL Server MVP]" wrote in message
news:%23AeQ8i4jIHA.1164@TK2MSFTNGP02.phx.gbl...
>> effect? I gather that it might be a good option if you are not IO bound
>> but have a CPU bottleneck.
>
> I am not using this trace flag. I can't think of a system I've ever seen
> that was not I/O bound (even those using EMC SANs).