SQL Server Error Log - A significant part of sql server memory has been paged out...

Question

SQL Server Error Log - A significant part of sql server memory has been paged out...

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Sachin Vaidya (9/30/2010)
Derrick Smith (9/30/2010)
96gb ram and you're maxing it out?
With a 96gig box, I'd limit the buffer cache to probably 80-82gb (the sql max memory setting).
Where did I mentioned that I have 96 gig of RAM !! I would have been the happiest person if that was true 😀

I believe it was inferred from this line in your first post.

Working set(KB): 97672

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sachin Vaidya Ten Centuries Points: 1273 More actions · Answer 2

Sachin Vaidya

Ten Centuries

Points: 1273

September 30, 2010 at 8:39 pm

#1229995

it is in KB

Sachin Vaidya Ten Centuries Points: 1273 More actions · Answer 3

getoffmyfoot (9/30/2010)
Use perfmon to figure out if SQL Server is the memory hog or SSRS... thats a starting point.
If you set max memory in sql server to 2gb, and ssrs has a max memory of 4, then you should be abiding well within your available RAM... Out of curiosity, what did you set the min to on both sql server and SSRS? Are there any other apps running on the SSRS machine?

I checked the perfmon data, when I did not set the max memory for SSRS it takes the whole available memory over a period of time and I left with no memory. When I set it to maximum memory for 4GB, my reports use within this and comes up successfully, but I still see the memory page out messages in large no. in SQL Server logs.

There is a different instance of SQL Server installed on this server but as of now in my knowledge, nobody is using that server apart from myself. And yes there is no other application running on it apart from SSRS.

Perry Whittle SSC Guru Points: 233866 More actions · Answer 4

Sachin Vaidya (9/30/2010)
There is a different instance of SQL Server installed on this server but as of now in my knowledge, nobody is using that server apart from myself.

Aah now we're getting somewhere. Doesn't matter if no one is using it, nature of the beast it will still eat memory. Try stopping the services for this instance of SQL and re run your reports.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

And stopping it will also tell you real quick if somebody actually IS using it, because you'll get a complaint! (Or something you have installed will stop working). 🙂

Thanks for the info about Lock Pages in Memory, I didn't realise they'd fixed that issue.

Perry Whittle SSC Guru Points: 233866 More actions · Answer 6

paul.knibbs (10/1/2010)
And stopping it will also tell you real quick if somebody actually IS using it, because you'll get a complaint! (Or something you have installed will stop working). 🙂

My guess is someone\something is using it!! 🙂 😉

paul.knibbs (10/1/2010)
Thanks for the info about Lock Pages in Memory, I didn't realise they'd fixed that issue.

Heh no problem man!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

Yeah, I have no idea where I got that 96gb from..too much multi-posting on my part 🙂

Even if nobody is using that second instance..if it is running, there is overhead for the process itself, plus the buffer cache..that could easily be over 2 gigs. Remember if you don't have max memory set, data will slowly accumulate over time and stay cached and keep going until you run out of memory.

Check sysprocesses and see if any people/apps are connected, and possibly run a trace on login/logout to see if anyone connects to it for a day or two. If you don't see anything, stop the service and wait to see if anyone complains.

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 8

I'd also keep an eye on your SSRS executionlog2 view. See if you can build a correlation between report executions and the paging messages in the log. You may find some poorly written report that is quietly chewing up memory while ssrs is processing the dataset.