March 24, 2011 at 5:26 am
Dear,
We got the following error in every 3-5 mins or sometimes more often:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1842 seconds. Working set (KB): 27832, committed (KB): 296576, memory utilization: 9%.
Five mins later
Working set (KB): 21356, committed (KB): 304924, memory utilization: 7%%.
So the sql server memory is reduced to 20Mb :F
Configuration:
Windows Server 2008 Enterprise Service Pack 2 (32bit)
Intel Xeon X3330 @ 2.66Ghz - 8,00Gb RAM
SQL Server 2008 Standard Edition
Nothing special running on the server sqlserver uses 200-360Mb before paged out, there are plenty of free memory in the system so there would be no need to page at all. According to the resource monitor Used physical memory is 35-40%
Do you have any idea what can cause this problem?
Best regards,
gimp
March 24, 2011 at 6:59 am
Do you have a limit on the SQL Server memory setting? I'd look there first.
Second, what kind of queries are being run?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2011 at 7:18 am
There are no limit on sql server memory usage, it's running with default settings:
Minimum was 0Mb increased it to 512Mb without any success
Maximum is: 2147483647Mb
March 24, 2011 at 7:30 am
Are there other softwares or utilities running on the machine?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2011 at 7:34 am
Have you looked ath this documentation from Microsoft? I'd start there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2011 at 8:14 am
I did, but we are using 32bit OS not 64bit, I read almost everything I found regarding this issue, but nothing helped, there are some other applications but there are plenty of free memory on the system so there is no sense to page out...
March 24, 2011 at 8:29 am
Ah, so, there are other apps running on the machine? That's more than likely the issure, right there. SQL Server does not like to play well with others. In almost all instances you have to put it on a server by itself.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2011 at 8:52 am
Yes, there are we can not afford to use a server dedicated for SQL only but as I mentioned there are more than 4G free memory and the sql server is using 150-200Mb which drops down to 20Mb! when memory paged... it's not realistic that because of an IIS and some smaller app which basically does nothing SQL would use 20MB...
March 24, 2011 at 8:55 am
But if some other app pages into memory, it's going to impact SQL Server, and hence the problem you're hitting. I'm not surprised. You're basically in a tough spot. You should put an upper limit on what SQL Server can allocate, and I'd make sure it's significantly below what the other apps are likely to use with some buffer for the OS itself. After that, you're really dependent on the OS.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2011 at 9:00 am
So there is no solution for this kind of problem, the system useage is minimal, page file is hardly used but the OS keep paging out memory... Aren't there any way to prevent sql memory to be paged, it could use 1-2G on it's will without any problem 😀
March 25, 2011 at 1:34 pm
I'd say it's rather unlikely, but the 'other' app(s) may be experiencing a memory leak. If you use Task Manager and see how much memory it/they are using, that should help rule in or rule out a problem with a memory leak. It should certainly give you a clearer picture of where the memory is being allocated. Another test for memory leak is to reboot. If after the memory is quickly constrained again, its probably not a memory leak in an app.
Also, look at where you swap/page file is placed. If you can afford to position it onto its own spindle, that can help tremendously in relieving memory constraints. Putting an old 40 or 80 gig drive in for swap/page activity can pay off big and such old drives are cheap and still available.
/Scott
March 28, 2011 at 8:04 am
Try "Lock Pages in Memory" 🙂
HTH
RK
March 28, 2011 at 8:17 am
As far as I know that is not available in Standard edition
March 28, 2011 at 8:39 am
Not sure of SQL 2008 base version but for For SQL Server 2008 R2 Standard Edition to use locked pages in buffer pool, you need to enable Trace Flag 845 as a startup parameter for the SQL Server instance.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
HTH
RK
March 28, 2011 at 11:47 am
I dont think Lock pages in memory is the answer, especially because of this.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Lock pages in memory is available for standard edition as of SQL Server 2005 SP3 CU4 and SQL Server 2008 SP1 CU2.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply