October 1, 2012 at 12:58 am
Hi
Operating system - Windows 2008 Enterprise Edition 32 bit.
SQL SERVER - MS SQLSERVER 2008 R2 32 bit.
Physical Memory - 8GB
whenever enabling AWE option for above setup production box. should be followed to enable PAE also?
Today i have faced issues for Full & log backup failed due to Error Ms 701, Level 17, There is insufficient system memory in resource pool 'default' to run this query.
Those backup taken after restart the SQL Services for timely resolved this issues.
how to fix it? should I enable AWE option?
Thanks
October 2, 2012 at 4:29 am
The /PAE boot switch in Windows enables the code you need. If you have hardware data protection turned on this also enables the PAE functionality so you do not need both switches but there is no harm if you do.
The Lock Pages in Memory right allows the SQL Server service account to make full use of the windows memory extention features. You need to give the service account this right. If you are running SQL Server Standard Edition you will also need trace flag 845
The AWE option in SQL Server allows SQL Server to use memory extention features.
This will allow you to use memory above the 4GB line, but doing this will cause Windows to need a little more memory. What happens in the background is that Windows creates a map for each 4KB page. The more memory you have the larger the map will be - it takes about 250MB for every 4GB memory above the 4GB line.
All the memory above the 4GB line is read-only. When you want to update something, SQL Server gets Windows to copy the 4KB page to a reserved space below the 4GB line. The page is then updated and copied back to its original location. This all sounds slow and complex and it is, but the whole process is much faster than getting data from disk.
These items are needed for 32-bit SQL Server editions prior to SQL 2012 when they run on 32-bit operating system. If you run on a 64-bit operating system the memory extention features are not available in Windows so you are limited to a maximum of 4GB memory within SQL Server. SQL 2012 32-bit also does not use windows memory extention, so you are limited to 4GB on any 32-bit SQL 2012 instance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 2, 2012 at 10:46 am
I STRONGLY recommend that you get a professional to give your system a performance review/health check. I am certain there will be quite a few findings that could be improved, and you can be mentored on what to watch out for and how to effectively monitor/tune the system going forward during the engagement. A good consultant can work wonders in just a few hours/days.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply