December 1, 2003 at 6:30 am
Hi!
I am running MS SQL 2000 Ent sp3 + MS Windows 2000 Datacenter on a server with 16Gb memory. Total available memory is typically ~200MB and pretty stable. I use Max Server Memory of 15700MB. Today I could see a very unusual behavior: the available memory gone from 200MB to 4MB in few seconds. I decreased the Max Server Memory by 200MB:
sp_configure [max server memory (MB)], 15500
reconfigure with override
go
It did not help much: it has 8MB available memory now. The server has a lot of paging.
What is happening to the server?
Thanks.
December 1, 2003 at 12:05 pm
From what I understand, above 8GB, you need to leave the OS 1GB for PAE.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 1, 2003 at 11:59 pm
But it did work fine for several months without disk queues and paging. I always had ~200MB of available memory...
December 2, 2003 at 2:00 am
Just another issue: after restarting SQL Server service it did not take any memory above 2GB! Now it has huge disk queues and slow erformance...
December 2, 2003 at 2:29 am
quote:
From what I understand, above 8GB, you need to leave the OS 1GB for PAE.
From http://support.microsoft.com/default.aspx?scid=kb;EN-US;274750 :
quote:
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.
December 2, 2003 at 4:25 am
Did you verify under Task Manager that SQL was using more than you alocated and check the Event Viewer logs for potential errors being inserted?
December 2, 2003 at 5:56 am
quote:
Did you verify under Task Manager that SQL was using more than you alocated and check the Event Viewer logs for potential errors being inserted?
Task Manager does not show memory usage correctly with AWE enabled, but Perfomance counter: Total Pages = 1932800 pages
1932800*8192/1024/1024= 15100
Max Server Memory =15100
SO MS SQL uses exatly what I allowed...
As for errors, found only one that could be related to this issue somehow:
quote:
Event Type:ErrorEvent Source:MSSQLSERVER
Event Category:(2)
Event ID:17055
Date:02.12.2003
Time:13:05:07
User:N/A
Computer:SATURNT
Description:
17122 :
initdata: Warning: Could not set working set size to 2879488 KB.
Data:
0000: e2 42 00 00 10 00 00 00 âB......
0008: 08 00 00 00 4a 00 55 00 ....S.A.
0010: 50 00 49 00 54 00 45 00 T.U.R.N.
0018: 52 00 00 00 00 00 00 00 T.......
December 2, 2003 at 6:12 am
Ok with the fact the value for max memory doesn't seem passed I would have to suggest some other source for the issue. Check the other errors around the time of the one you posted (2 hours both ways is as far as I would go) since that seems to be a poitn where SQL suffers an memory allocation issue. Also, using task manager look at what all is running in memory and verify all unneeded services are off. If you have any specialized services such as a virus scanner stop for a while and see if anything gives a spark of life back that might account for your issue. Start with anything your recently installed and recently updated. Also, look at the fragmentation of all you drives and use the Error-checking piece on the Tools tab of each drives properties dialog to see if maybe a drive has an unreported issue and may be thrashing the IO and other peices. Mack sure your windows environment is configured to give performance to background services as opposed to applications. Lastly if you are not running an Anti-Virus solution go out to the Symantec website and ue there virus web based virus checker to see if anything maybe got in you did not realize.
December 2, 2003 at 7:50 am
Are you running any linked server queries to a non-server datasource, like foxpro or something?
If that 128mb(?) of memory it allows gets used up by multi-user or ineffecient table scans on the foxpro/other external table, it can have very strange behavior and memory allocation errors. Any table of a decent size will fry it if it can't find an index to use.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply