July 8, 2011 at 2:57 am
My post meant that when using a 32 bit install of sql you need to enable AWE to address additional memory and in order to enable AWE you need to have lock pages in memory enabled.
http://technet.microsoft.com/en-us/library/ms190673.aspx
Before enabling AWE, you must configure the Lock Pages in Memory policy. For more information, see How to: Enable the Lock Pages in Memory Option (Windows).
July 8, 2011 at 11:59 am
GilaMonster (7/8/2011)
Jayanth_Kurup (7/7/2011)
Since your using 64 bit SQL Server you actually do not need to enable lock pages in memory to avoid paging , mainly since sql will be able to address all the memory as it is.Locked pages has nothing to do with addressing memory. It's the AWE setting that's not required on 64-bit because SQL can directly address all the memory.
Gail,
I could have sworn that I read an article here on SSC not too long ago stating that even on 64-bit systems, enabling AWE and Lock Pages in Memory gave a performance boost. I seem to recall it had to do with the API's for AWE having been optimized.
Please correct me if I'm wrong.
Todd Fifield
July 8, 2011 at 12:25 pm
AWE is ignored completely on 64-bit.
Locked pages has a use on both, but it is not related to memory addressability.
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
July 8, 2011 at 1:22 pm
GilaMonster (7/8/2011)
AWE is ignored completely on 64-bit.Locked pages has a use on both, but it is not related to memory addressability.
Gail,
Thanks - I'll keep it in mind. How about a 32-bit SQL Server instance running on a 64-bit machine?
Todd Fifiled
July 8, 2011 at 1:29 pm
That's 32-bit. Hence it needs AWE or it can only address 4GB.
A 32-bit process needs AWE to address memory above the 4GB boundary
A 64-bit process can address memory up to 16 exabytes, ignores the AWE setting.
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
July 8, 2011 at 1:48 pm
tfifield (7/8/2011)
GilaMonster (7/8/2011)
Jayanth_Kurup (7/7/2011)
Since your using 64 bit SQL Server you actually do not need to enable lock pages in memory to avoid paging , mainly since sql will be able to address all the memory as it is.Locked pages has nothing to do with addressing memory. It's the AWE setting that's not required on 64-bit because SQL can directly address all the memory.
Gail,
I could have sworn that I read an article here on SSC not too long ago stating that even on 64-bit systems, enabling AWE and Lock Pages in Memory gave a performance boost. I seem to recall it had to do with the API's for AWE having been optimized.
Please correct me if I'm wrong.
Todd Fifield
I think the article you are referring to is a CSS blog which can be accessed via the URL I posted in my earlier reply in this thread. this refers to the AWE APIs having a beneficial effect with 64bit in the kernel.
However the sp_configure option for AWE has absolutely no effect in a 64bit instance and is ignored.
---------------------------------------------------------------------
July 8, 2011 at 1:53 pm
george sibbald (7/8/2011)
I think the article you are referring to is a CSS blog which can be accessed via the URL I posted in my earlier reply in this thread. this refers to the AWE APIs having a beneficial effect with 64bit in the kernel.
If I'm not mistaken, those APIs are used with locked memory pages (hence why locked pages is needed for AWE on 32-bit SQL)?
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
July 8, 2011 at 2:10 pm
George and Gail,
Thanks for your replies. It helped clear up a confusion I had on 32-bit SQL Server running on a 64-bit OS.
Todd Fifield
July 8, 2011 at 2:53 pm
GilaMonster (7/8/2011)
george sibbald (7/8/2011)
I think the article you are referring to is a CSS blog which can be accessed via the URL I posted in my earlier reply in this thread. this refers to the AWE APIs having a beneficial effect with 64bit in the kernel.If I'm not mistaken, those APIs are used with locked memory pages (hence why locked pages is needed for AWE on 32-bit SQL)?
its to enable a 'locked pages' feature with 64 bit, but the microsoft engineers will explain it better than I ever could so again I recommend reading this URL for the full story
in which they state:
the developers of the product (64bit SQL) discovered that if they still use the AWE APIs to allocate memory even though it is not really needed, two things would happen:
A small performance gain occurs within the kernel. For more details, read this blog post from Slava Oks: http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
Just as with 32bit systems, any memory allocated using the AWE API is not part of the working set and therefore cannot be paged to disk. Therefore it is considered “locked”.
Thus was born the concept most refer to as “locked pages” for 64bit SQL Server editions.
Now remember the requirement to use the AWE APIs for any Windows application? The user account running the process must have the “Locked Pages in Memory” privilege set. So in the 64bit SQL Server engine, if this privilege is set ...(+ trace flag 845 for standard edition)...we internally use the AWE APIs to allocate memory. By using the AWE APIs, we have in effect enabled a “locked pages” feature for the SQL Server engine.
So in summary the AWE APIs for 32bit and 64bit SQL Server systems are used for different purposes. In 32bit it is really to extend memory access beyond 4Gb or to enable the AWE feature. For 64bit systems, it is to possibly gain performance and to “lock pages” for the buffer pool.
---------------------------------------------------------------------
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply