November 16, 2007 at 11:15 am
I have a severe problem with my sql server installation. The server:
Microsoft Windows Serer 2003
Standard x64 Edition
Service Pack 2
12.8GB of Ram
Sql Server 2005 - 9.00.3054.00 (X64)
I have 10GB allocated to SQL Server at min and max which leaves 2.8 for the o/s. There are no other applications running on the server.
What I am seeing in the even log is the following error
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 612 seconds. Working set (KB): 2899800, committed (KB): 10820400, memory utilization: 26%.
The error varies and can log many entries over a variable period of time:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7212 seconds. Working set (KB): 4708544, committed (KB): 10820400, memory utilization: 43%.
We have already tried adjusting min/max memory lower with even worse results.
I have looked at various sources and articles.
(KB): 24484 - maybe trimming (hard)
920739 - it's not this (copy files > 500mb)
931308 - It's not SAP 3
Seems to address the problem with ClearCache.
Anyway, please help if you can!
November 19, 2007 at 2:03 pm
Is this an HP box? They have a very nasty bug in their iLO driver that causes RAM flushing too, just like the large file copy bug.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 26, 2007 at 1:46 pm
I'm not sure that this is the problem, but I understand that you must have the lock pages in memory permission assigned to the SQL account, or windows can page out SQL memory to the page file. This is something that should be pretty easy to check, but I this may not be the problem. One white paper that will help you troubleshoot the problem is the following:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
Personally, one of my favorite white papers...
Eric
November 26, 2007 at 2:07 pm
Lock Pages in Memory only applies to Enterprise Edition of SQL Server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 26, 2007 at 2:12 pm
Copying *large* files (like backup files 😉 ) across the network causes such problems. I have struggled with M$ support to no avail. My solution was to upgrade to Enterprise Edition 🙁
If you find something "else" that can be done, please let us know 😀
* Noel
November 26, 2007 at 2:33 pm
TheSQLGuru (11/26/2007)
Lock Pages in Memory only applies to Enterprise Edition of SQL Server.
Good call.
November 26, 2007 at 2:46 pm
noeld, did the entire sql server ram get flushed (I must admit it was pretty amazing seeing physical ram go from 9.2GB to 0 bytes instantaneously!! 🙂 or was it just some portion and you started paging heavily when you did large network copies? Also, did you have the large-file-copy bug hotfix applied??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 26, 2007 at 4:13 pm
yes I thought the hotfix would help ... but it was just cr@p. My files are 100GB + so the OS memory cache somehow tries to "help" me when the copy is running and the solution that it takes is to hard-trim all other processes. that, absolutely SUCKS. There is almost no control over how the OS can be stopped about doing that.
* Noel
November 26, 2007 at 7:38 pm
Did you try the ESEUtil program that comes with exchange?
http://blogs.technet.com/askperf/archive/2007/05/08/slow-large-file-copy-issues.aspx
November 27, 2007 at 4:45 pm
Have you checked the Tokencache?
SELECT GetDate() as TimeofCapture, SUM(single_pages_kb + multi_pages_kb) as "CurrentSizeOfTokenCache(KB)"
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
We had an issue where our server just ended up being very slow and had lots of paging, it turned out that the Tokencache (permissions and execution plans) was causing real data to be paged out. This bug was supposedly fixed in build 3161 but we didnt see it. We clear the token cache when it reaches a certain level (~200mb)
DBCC FREESYSTEMCACHE('TokenAndPermUserStore')
November 28, 2007 at 7:09 am
Here's the KB article for anyone interested: http://support.microsoft.com/kb/927396
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 28, 2007 at 2:04 pm
Dave I (11/26/2007)
http://blogs.technet.com/askperf/archive/2007/05/08/slow-large-file-copy-issues.aspx%5B/quote%5D
Dave, yes we are replacing most our copy utilitites with eseutil. it rocks
There is still one problem, sometimes we want to "restore" accross the network and
that is when the OS kill us.
* Noel
November 29, 2007 at 6:08 pm
Does this help?
http://support.microsoft.com/kb/918483
Based on some of the research I have done, this seems to be an open issue...we saw it on 32bit on Build 3186. Hard coding max/min memory settings seemed to help it.
Thanks, Craig
March 12, 2008 at 8:59 am
[font="Arial"]I am encountering similar issues on 2 of 3 nodes in our Clustered SQL 2005 Enterprise x64 environment. It appears to happen when Tivoli Storage Manager is writing from our SAN backup volumes to tape. Paging goes through the roof, available memory runs out. However, per KB920739, this occurs when writing from fast disk to local slow disk - we're going to tape. Can this still be the cause?
Specs for our cluster nodes are:
16gb RAM
8 proc dual core
Windows 2003 64-bit sp2
SQL 2005 x64 Enterprise SP2
We are receiving errors such as the following that lead me to believe this is a similar issue: [/font][font="Times New Roman"]
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 944 seconds. Working set (KB): 453404, committed (KB): 7299576, memory utilization: x%.
-or-
Resource Monitor (0x3ac) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 128712 KB. Approx CPU Used: kernel 1156 ms, user 343 ms, Interval: 122719.[/font]
The service account for SQL is a local admin on the cluster node so we should not need to assign it "Lock Pages in Memory" rights, as it should already have that right as a local admin. I'm at a complete loss. Any help is not only appreciated, but I will worship your skills from afar if you can lead me to a solution.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 13, 2008 at 1:27 pm
Tim, I recommend calling Tivoli and asking if they have any known issue that could be causing this or a patch to address it. Then call Microsoft support (for a small fee) and get them involved if Tivoli didn't help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply