October 11, 2012 at 1:00 pm
Considering a SQL 2008 R2 Enterprise Edition, running on it's own dedicated physical server with > 32GB RAM, what are the pro/cons of enabling this? Will some things run more efficiently? I'm not going to use trace flag 834. If not using that trace flage, would some pieces of SQL still use large pages for certain things? Is it worth enabling lock pages in memory for this if the trace flag isn't used? Anyone know or have any experience with it? Thanks!
October 11, 2012 at 1:30 pm
I recommend starting with this article: http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
Research from there. It's a complex subject, in its own way, with a lot of different opinions backed by a lot of the same facts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2012 at 2:41 pm
I don't recommend enabling large pages, it can have some effects at startup and it doesn't usually help much, if at all
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
October 12, 2012 at 2:30 am
GilaMonster (10/11/2012)
I don't recommend enabling large pages, it can have some effects at startup and it doesn't usually help much, if at all
+ 1
When you enable large pages SQL Server will try to acquire the memory defined in max server memory, if it can't SQL Server won't start.
I have seen this trace flag recommended though in the Fast Track Data Warehouse 3.0 Reference Guide
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2012 at 2:38 am
Perry Whittle (10/12/2012)
GilaMonster (10/11/2012)
I don't recommend enabling large pages, it can have some effects at startup and it doesn't usually help much, if at all+ 1
When you enable large pages SQL Server will try to acquire the memory defined in max server memory, if it can't SQL Server won't start.
It will start, it'll just allocate the largest contiguous block it can and will never grow buffer pool after that. Can also take ages to start because it's trying repeatedly to reserve memory.
http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
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
October 12, 2012 at 4:16 am
GilaMonster (10/12/2012)
It will start, it'll just allocate the largest contiguous block it can and will never grow buffer pool after that. Can also take ages to start because it's trying repeatedly to reserve memory.http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
Yes, I've read that article before but that article as well as this one, state that the SQL Server instance may fail to start if the lowest portion of contiguous memory cannot be allocated, something that may happen when SQL Server is not the only rooster in the henhouse 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2012 at 5:43 am
That would require that the largest portion of contiguous memory available is under 512 MB. If that's the case, I suspect there's more of a problem than just SQL Server not starting.
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
October 12, 2012 at 5:51 am
oh for sure, I don't doubt that. But, all the same, these things can and do happen
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2012 at 9:09 am
Good reads, thanks for the information.
So do you guys think this will be used more effectively in future versions of SQL?
October 12, 2012 at 9:49 am
Honestly, I would doubt it. As far as I know, the traceflag was documented because it was used in a TPC benchmark, and they require that all options be documented. But those are often sooooo carefully set up that they don't really resemble normal environments.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply