September 24, 2008 at 9:19 am
SQL 2k5 SP2 standard edition
W2k3 SP2
10gb RAM
i have a box running SQL and SQLRS and it's having MAJOR memory management issues. about a week and a half ago or so, i started noticing in the logs that it was creating dump files. it wasn't very often, about once per day, usually overnight, but it was more than enough to get some attention. i couldn't find anything obvious, and in the meantime, the problem started getting worse. i requested, and finally got, 4gb of ram to bring it up to 10gb. after that was installed, the machine more or less went haywire. the nightly crashes went away, but they were replaced by this:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation. "
this happened constantly (about once every 10 minutes), so i dropped everything else, put everyone on hold and dove in. i monitored counters, ran traces, read log files and found that it wasn't a sql issue... sql was a victim.
the system is constantly experiencing hard page faults in the cache, and every once in a while, it dumps the entire cache to the page file. at the same time, most of the applications, including sql, give up 90% of their RAM. SQL RS, however, doesn't. it keeps its RAM and occasionally takes more.
after some tweaks, the error message now shows about once an hour or so instead of constantly, and people are able to at least get some work done.
i can't lock pages in memory because it only applies to enterprise edition (but i tried it anyway). i've set min/max server memory to 4gb/7.5gb (among other values, some higher, some lower). i've made sure the system is managing its own page file and allocating system memory for programs. i've even modified SQL RS' configuration files so that it knows not to take more than 2.5gb of RAM (but for some reason, it's ignoring that even though the logs show it's reading and using those settings... it STILL climbs to 3gb+).
in performance monitor, i'm watching:
%processor
SQLRS' working set
SQL's working set
data flushes
available MB
cache bytes
cache faults
system cache
when memory takes a dump, SQL's working set drops to <100mb, SQLRS' working set either stays the same or climbs, available MB and data flushes and cache faults go through the roof and everything else drops.
i need help... i don't know why SQL RS is ignoring the config settings. i don't know why the system is dumping its cache and freeing up RAM when SQL is obviously using it.
September 24, 2008 at 11:06 am
Is it 32-bit or 64-bit SQL Server Edition? Have you tried changing the max server memory to 5.5 or somewhere around 6.0? Also, try changing min server memory to 2 or 2.5 GB.
Check the following too:
1. Lock pages in memory local group policy set for the SQL Server service account.
2. AWE (yes, even in 64 bit) enabled in SQL Server 2005 configuration.
What you see under System Cache Resident Bytes under Memory performance counter? After some googling I found that file system cache was the main culprit which was expanding like anyhting and was fetching RAM from SQL.
MJ
September 24, 2008 at 11:17 am
i knew i'd forget something important. it's a 64bit system. i've set min/max memory pretty much all over the board to test different configurations. i've locked pages in memory, but since i'm running standard edition, it's not picking it up and using it. and AWE has been enabled and disabled (currently enabled).
system cache resident bytes is holding around 43mb. it gets as high as 150mb, but nowhere near the 1.5gb limit i set before it's dumped. i found similar posts, which is why i'm watching that counter, but i don't think that's the issue here... it never gets above about 150mb, even when it's going through a TON of hard page faults.
incidentally, i did make one more change to SQL RS' config file, and for now at least, it looks like it's stable... it hasn't issued that error message since my earlier post. as of this post, SQL RS is averaging 780mb under full load. i'm still seeing fault spikes, but they're few and far between and have no significant impact on RAM...
this is me knocking on wood...
September 24, 2008 at 11:35 am
ok, so knocking on wood wasn't the answer... it just got dumped again. any suggestions are welcomed...
September 24, 2008 at 12:11 pm
Can you please clarify what config value for SSRS did you change? From what to what?
Also, AWE on x64 based OS with x64 based SQL server does not have any effect.
September 24, 2008 at 12:13 pm
Have you tried by disabling antivirus, system level tasks that are running outside of SQL? Might be faulty dirver sort of issue...
MJ
September 24, 2008 at 12:16 pm
per microsoft's document (http://msdn.microsoft.com/en-us/library/ms159206.aspx), i've been playing around with these settings in c:\program files\microsoft sql server\mssql.3\reporting services\reportserver\rsreportserver.config:
(MemorySafetyMargin)20(/MemorySafetyMargin)
(MemoryThreshold)30(/MemoryThreshold)
(WorkingSetMaximum)1000000(/WorkingSetMaximum)
(WorkingSetMinimum)500000(/WorkingSetMinimum)
the above values are the currently running values, and it seems to be much more stable with this configuration than with the original configuration.
as for awe, i'm at the point where i'm willing to give everything that looks like it might work a shot, even if all the documentation in the world says it doesn't have any effect.
September 24, 2008 at 12:17 pm
manu,
i've done that. at the moment antivirus is running because it didn't have any effect enabled or disabled. i've also updated the network drivers; i'd read somewhere that people were experiencing this issue with broadcom network cards...
September 24, 2008 at 12:25 pm
Did you try this one?
September 24, 2008 at 1:43 pm
i have not... yet. i'll need to do some research into it first, but if it looks like it might solve my issue, i'll look into applying it.
thanks...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply