March 10, 2010 at 1:42 am
Hi, I'm running Sql Server 2008 Std x64 SP1 on a Windows 2003 Std R2 SP2 x64 with 32GB. No Max/min memory are set in Sql Server.
Every night there is a job running and sometimes it failes with this message in the historylog of the job:
"There is insufficient memory available in the buffer pool"
In the errorlog it says:
"Error: 701, Severity: 17, State: 123"
"There is insufficient system memory in resource pool 'internal' to run this query"
"Failed allocate pages: FAIL_PAGE_ALLOCATION 1"
"A significant part of sql server process memory has been paged out. This may result in a performance degradation"
Any ideas?
March 10, 2010 at 1:55 am
Similar issues:
http://www.sqlservercentral.com/Forums/Topic672649-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic715463-146-1.aspx
I believe you will get the answers from those posts.
Good luck!
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 10, 2010 at 7:51 am
There is no problem with the tempdb space but I have splitted the tempdb into 8 physical files and the server has 4 quadcore cpu.
Can that be an issue? Best practice is one tempdb physical file per cpu = 16 files on my server.
Another thing is that the "Lock pages in memory" not is set. That option exists in
Sql Std Server 2008 SP1 Cumulative Update 2. Anyone tried this on Std x64?
March 10, 2010 at 8:42 am
May be due to XPs and distributed queries, as you are on SQL 2008 64 bit, G switch settings will not help, I would say call Microsoft support rather than wasting time to research.
March 23, 2010 at 3:36 am
The solution from MS support was to set the maximum server memory option to total ram - 5GB.
The 5 GB are dedicatied to the OS and that is a rec for x64-systems.
Hope this will help.
March 23, 2010 at 6:50 am
Since this is SQL Server 2008 x64 Standard Edition, it seems you are suffering from paging of SQL Server memory.
See http://support.microsoft.com/kb/970070 for the fix that allows x64 Standard Edition to 'lock pages in memory' just like 32-bit Standard Edition can (through AWE).
Assuming the server is dedicated to SQL Server, you should set max server memory to around 28GB (leaving 4GB for the OS and other processes) and monitor. It may be possible to increase that to 30GB longer term, but you need to test.
About tempdb multiple files. You should only create extra equally-sized files if you can prove that you have been suffering from allocation structure contention. Even then, only create a quarter to one half as many files as you have cores, with a sensible maximum of eight files in total in any circumstances. See http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply