September 28, 2010 at 4:22 pm
Hello,
I have been facing this issue since past few weeks.
My SQL Server Logs show this message
"A significant part of sql server memory has been paged out. This may result in performance degradation. Duration: 300 seconds. Working set(KB): 97672, memory utilization 42%"
This message is repeats in almost every 5 minutes for whole day and the numbers keep changing everytime.
I have read many solutions for this online and implemented but nothing is working.
I tried following as suggested;
- Set max server memory setting for SQL Server.
- I am using SSRS so I also set the maximum memory limit for report server.
- Set lock pages in memory option for my user name.
- Disable TCP Chimney, TCPA, and RSS using the registry settings (I have not implemented this because I do not have fair knowledge about
registry settings and I do not want to risk my server.)
Any inputs on resolving this issue will be a great help.
Thanks.
September 29, 2010 at 11:27 am
Why did you set lock pages in memory for your user account? Is that the account Sql server service is running under?
September 29, 2010 at 12:00 pm
Yes I realized that this setting is not worth as we need to set it for the user account that is assigned to SQL Server service.
Though I am fetching the data from a remote server, and displaying it in SSRS 2008 report which is present on the server in qiestion.
So, should I set the lock pages in memory option for SSRS service working set?
Regards,
Sachin
September 29, 2010 at 12:18 pm
What OS are you running (32-bit/64-bit)?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 29, 2010 at 5:05 pm
Sorry I didn't mentioned earlier,
I am using Win Server 2003 R2 64 bit SP2 and SQL Server 2008 R2 64 bit.
Thanks.
September 30, 2010 at 3:29 am
What edition of SQL Server? Pretty sure the "lock pages in memory" thing only works on Enterprise Edition (or Developer Edition, which is essentially the same thing with more restrictive licensing).
September 30, 2010 at 7:40 am
SQL Server 2008 R2 64 bit Enterprise Edition
September 30, 2010 at 11:24 am
Sachin Vaidya (9/29/2010)
Yes I realized that this setting is not worth as we need to set it for the user account that is assigned to SQL Server service.Though I am fetching the data from a remote server, and displaying it in SSRS 2008 report which is present on the server in qiestion.
So, should I set the lock pages in memory option for SSRS service working set?
Regards,
Sachin
lock pages only applies to the buffer pool memory. You have an instance of SQL Server database engine and SSRS on the same server is that correct?
paul.knibbs (9/30/2010)
What edition of SQL Server? Pretty sure the "lock pages in memory" thing only works on Enterprise Edition (or Developer Edition, which is essentially the same thing with more restrictive licensing).
For reference SQL Server 2008 SP1 CU2 and SQL Server 2005 SP3 CU4 provide support for lock pages in memory on standard edition of SQL Server. The version here is 2008 R2 so doesnt apply anyway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 30, 2010 at 1:29 pm
My source database for reports is a SQL 2005 database which is remote.
September 30, 2010 at 2:36 pm
How much memory is available on the server?
What have you set sql max memory to?
Have you maxed out the memory slots in the machine?
September 30, 2010 at 2:43 pm
Do you have database traces for that period of time? I think a trace may be revealing. It could show if you have a very memory intensive transaction or something similar occurring that could cause the memory to exceed its limits. I'd also start collecting perfmon statistics.
September 30, 2010 at 3:12 pm
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).
September 30, 2010 at 5:50 pm
MG-253362 (9/30/2010)
How much memory is available on the server?What have you set sql max memory to?
Have you maxed out the memory slots in the machine?
8GB RAM is the total memory on the server.
Since I am running heavy reports using SSRS and my data source is remote, I have set maximum memory limit of 4GB to report server.
2 GB is set to local sql instance as max memory.
When I start running the reports one by one (current report finishes after that there is a wait period of atleast 20 minutes so that report server releases the memory back and then only next report is executed)they come up successfully (various reports from range of 300K records to 2 million records). So, I am getting reports successfully but I am also getting these memory page out messages in my SQL Server log on the local machine (where SSRS is installed).
September 30, 2010 at 5:53 pm
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 😀
September 30, 2010 at 6:39 pm
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?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply