October 3, 2011 at 5:55 pm
Wondering if anyone successfully implement the large-page allocations for the buffer pool by Trace flag 834 ?
Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool.
We have sql server 2008 R2 SE running on windows 2008 R2 64bix two nodes cluster, i did:
1) sql server service acccount has Lock Pages in Memory on both nodes;
2) put -T834 on instance start parameter on both nodes;
when i check: DBCC TRACESTATUS
TraceFlag Status Global Session
834 1 1 0
but:
select large_page_allocations_kb, locked_page_allocations_kb from sys.dm_os_process_memory
large_page_allocations_kb locked_page_allocations_kb
0 0
Not able to make it work ? Any thoughts ? Is it have to be EE not SE ? Thanks.
ddfg
October 4, 2011 at 4:18 am
First question. Why are you trying to use it?
http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
Quote from that post:
So in summary:
* Large page support is enabled on Enterprise Edition systems when physical RAM is >= 8Gb (and lock pages in memory privilege set)
* SQL Server will allocate buffer pool memory using Large Pages on 64bit systems if Large Page Support is enabled and trace flag 834 is enabled
* Large page for the buffer pool is definitely not for everyone. You should only do this for a machine dedicated to SQL Server (and I mean dedicated) and only with careful consideration of settings like ‘‘max server memory’. Furthermore, you should test out the usage of this functionality to see if you get any measureable performance gains before using it in production.
* SQL Server startup time can be significantly delayed when using trace flag 834.
It's not a general setting anyone should be using, it's something that needs careful consideration, careful monitoring and should be used with caution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply