May 20, 2009 at 2:32 pm
I am having a strange dilemma where the page life expectancy counter is dropping to around 200-300 every hour or so.
The available memory at that time is around 1.5 Gb. The total ram is 8GB and the o/s is windows 2003 32bit, dual core xeoon system. So I am not sure why the counter is dropping so low. Any ideas ? This is around the same time where we have heavy traffic and system is getting hit hard.
Thanks!
May 20, 2009 at 2:48 pm
Is AWE enabled? With 32-bit OS you need to enable usage of memory over 2Gb. Usually, if server has 8Gb doesn’t mean SQL Server is using it.
Also, check article: http://sqlserverpedia.com/blog/sql-server-2005/i-had-a-question-regarding-page-life-expectancy-in-sql-server-2005-what-causes-page-life-expectancy-to-be-very-low-if-you-have-low-page-life-expectancy-how-is-it-resolved-and-avoided/ that has great info about page-expectancy.
Alex Prusakov
May 20, 2009 at 3:08 pm
Are you running Windows Enterprise Edition so that SQL Server can use more than 4 GB of memory?
May 20, 2009 at 3:13 pm
So the server is actually windows 2003 standard edition which only supports up to 4GB. So that's not good. Secondly AWE is not turned on either so I think that's the reason for the Page Life Expectancy to be low. I will try turning on the AWE setting and see if that helps. Thanks for all the quick responses!
May 20, 2009 at 4:50 pm
kompwiz (5/20/2009)
I am having a strange dilemma where the page life expectancy counter is dropping to around 200-300 every hour or so.The available memory at that time is around 1.5 Gb. The total ram is 8GB and the o/s is windows 2003 32bit, dual core xeoon system. So I am not sure why the counter is dropping so low. Any ideas ? This is around the same time where we have heavy traffic and system is getting hit hard.
Thanks!
I recently had one that went from 500 to 5 every hour.
Usually when this happens I run the profiler at the time the incident occurs and find that some process I hadn't seen before does LOTS of logical reads that are also LOTS of physical reads and chucks all the rest of the cached data out.
I find the simplest to see this is to track the SP:CacheRemove event and look for the SPID that does it. Then fix whatever that does,
Works 90% of the time, hope it does for you.
Tim
.
May 20, 2009 at 5:06 pm
You didn't post your database size. But here are my comments.
Alex Prusakov (5/20/2009)
Is AWE enabled? With 32-bit OS you need to enable usage of memory over 2Gb.
Michael Valentine Jones (5/20/2009)
Are you running Windows Enterprise Edition so that SQL Server can use more than 4 GB of memory?
I think relying on memory and AWE can be lazy unless the business requirement supports it.
I try and keep mine to the smallest footprint possible.
We run many databases in the 100-500GB range with 4GB of memory. Windows set to 3GB for applications. One has 1000-1500 connections. No problems.
I think I am sometimes too reluctant to add memory. But frankly I would prefer the developers concentrate on the best bang per buck every time.
Stick with a small footprint!
Tim
.
May 20, 2009 at 10:03 pm
Tim Walker (5/20/2009)
You didn't post your database size. But here are my comments.Alex Prusakov (5/20/2009)
Is AWE enabled? With 32-bit OS you need to enable usage of memory over 2Gb.Michael Valentine Jones (5/20/2009)
Are you running Windows Enterprise Edition so that SQL Server can use more than 4 GB of memory?I think relying on memory and AWE can be lazy unless the business requirement supports it.
I try and keep mine to the smallest footprint possible.
We run many databases in the 100-500GB range with 4GB of memory. Windows set to 3GB for applications. One has 1000-1500 connections. No problems.
I think I am sometimes too reluctant to add memory. But frankly I would prefer the developers concentrate on the best bang per buck every time.
Stick with a small footprint!
Tim
You are incredibly lucky to be able to push development team in the right direction. Most of the time cost of additional memory or even powerful server less than cost of the proper application development. If you have third party application (for example, old version of Dynamics) there is no way to get good performance without investment in the hardware.
Also, the question was why page-expectancy life is short and not what the development team can do better… 🙂
Alex Prusakov
May 21, 2009 at 12:59 am
Tim Walker (5/20/2009)
You didn't post your database size. But here are my comments.Alex Prusakov (5/20/2009)
Is AWE enabled? With 32-bit OS you need to enable usage of memory over 2Gb.Michael Valentine Jones (5/20/2009)
Are you running Windows Enterprise Edition so that SQL Server can use more than 4 GB of memory?I think relying on memory and AWE can be lazy unless the business requirement supports it.
I try and keep mine to the smallest footprint possible.
We run many databases in the 100-500GB range with 4GB of memory. Windows set to 3GB for applications. One has 1000-1500 connections. No problems.
I think I am sometimes too reluctant to add memory. But frankly I would prefer the developers concentrate on the best bang per buck every time.
Stick with a small footprint!
Tim
Well, since the system already has 8 GB of memory installed, it would be nice to be able to actually use more than 1.5 GB for SQL Server, especially when SQL Server is already showing signs of memory pressure.
It only takes one large table scan to use up all the memory, and it may not be easy to fix the process so that it doesn't. Spending the money to upgrade to Windows Enterprise edition would let them up SQL Server to 6 GB and have 4 times the current amount. It might not solve every problem, but it is likely to be a significant boost in performance for a relatively small cost.
May 21, 2009 at 1:38 am
Alex, Michael, you are both correct.
And in hindsight I regret the way I phrased my comments to you.
Sorry.
Tim
.
May 21, 2009 at 10:48 pm
Alex Prusakov (5/20/2009)
Is AWE enabled? With 32-bit OS you need to enable usage of memory over 2Gb. Usually, if server has 8Gb doesn’t mean SQL Server is using it.Also, check article: http://sqlserverpedia.com/blog/sql-server-2005/i-had-a-question-regarding-page-life-expectancy-in-sql-server-2005-what-causes-page-life-expectancy-to-be-very-low-if-you-have-low-page-life-expectancy-how-is-it-resolved-and-avoided/ that has great info about page-expectancy.
You don't need AWE enabled to access more than 2 GB of RAM on a 32-bit OS. We have several 32-bit systems running with 8 GB of RAM, and 2 instances of SQL Server on each using 3 GB RAM each. We just needed to add the /3GB switch to the boot.ini file.
If we had 1 instance of SQL Server running on these servers and wanted to access more memory, then we would need to enable AWE.
May 28, 2009 at 6:21 am
Lynn Pettis (5/21/2009)
You don't need AWE enabled to access more than 2 GB of RAM on a 32-bit OS...we just needed to add the /3GB switch to the boot.ini file.
This is a fascinating subject!
It is true that it is not required to enable AWE to allow SQL Server to make use of more than 2GB physical memory, but it is often desirable, at least on SQL Server 2005 on Windows 2003.
With /3GB, the entire buffer pool is mapped into SQL Server's virtual address space (VAS). This often leads to VAS pressure - frequently while plenty of free physical memory remains available. VAS pressure is difficult to detect, and difficult for SQL Server to respond to in non-AWE mode.
With AWE enabled, the buffer pool (and therefore all single-page allocation) uses nonpaged pool. This is accessed via a small VAS-mapped window. In AWE mode, SQL Server allocates VAS in 4MB chunks, and can release these if it detects VAS pressure.
A 32-bit SQL Server with a 2GB user VAS using AWE is generally much to be preferred over the /3GB switch alternative, even where physical memory is 4GB or less, and even where physical memory is smaller than the user VAS.
Paul
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
http://technet.microsoft.com/en-us/library/ms190731(SQL.90).aspx
http://blogs.msdn.com/slavao/archive/2006/08/03/687573.aspx
http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2009 at 7:02 am
Paul White (5/28/2009)
Lynn Pettis (5/21/2009)
You don't need AWE enabled to access more than 2 GB of RAM on a 32-bit OS...we just needed to add the /3GB switch to the boot.ini file.This is a fascinating subject!
It is indeed! Unfortunately I'm not quite getting the point you are making .. my fault, not yours!
My (limited) understanding of adding the /3GB switch is that it makes 3GB available to applications as a privately addressable space leaving 1GB for the O/S and shared memory. The bit I think I don't get is what the VAS pressure you refer to is and why changing the memory boundary would cause it.
Is it that SQL grabs potentially more than its share of the 3GB VAS and won't release it when other processes need private space of their own? In other words a problem for machines that are not just running a single instance SQL Server and nothing much else?
Or is it that 1GB isn't really enough to leave for the O/S and shared memory?
Or perhaps I have missed the point completely!
Tim
Edit: Corrected typo
.
May 28, 2009 at 3:38 pm
Tim Walker (5/28/2009)
It is indeed! Unfortunately I'm not quite getting the point you are making .. my fault, not yours!
Hey Tim - no worries, let me try to answer your questions:
Tim Walker (5/28/2009)
My (limited) understanding of adding the /3GB switch is that it makes 3GB available to applications as a privately addressable space leaving 1GB for the O/S and shared memory. The bit I think I don't get is what the VAS pressure you refer to is and why changing the memory boundary would cause it.
Each process has its own virtual address space (VAS) - by default user-mode and kernel get 2GB of VAS each. Remember that this bears no relation to physical memory - this is the memory address space a process can see - Windows takes care of mapping VAS allocations to physical memory as required.
Tim Walker (5/28/2009)
Is it that SQL grabs potentially more than its share of the 3GB VAS and won't release it when other processes need private space of their own? In other words a problem for machines that are not just running a single instance SQL Server and nothing much else?
Since each process gets its own VAS, the issue isn't one of 'sharing'. In non-AWE mode, when SQL Server starts up it leaves just 256MB of VAS for external processes (the misnamed MemToLeave area). It also uses 128MB VAS for its own threads (default 256 threads with 512KB stack each). All remaining VAS is allocated to the Buffer Pool, assuming that physical memory is at least as large as the VAS. Buffer pool is then used for all 8KB page single allocations. Anything larger than 8KB must be allocated from the 256MB area.
Simplifying greatly, this means that the available user VAS (be it 2GB or 3GB) is largely allocated (and typically fragmented) right from the word go. The availability of contiguous regions of VAS typically continues to decline with time as normal allocations and deallocations take place. This can result in Windows not being able to allocate future requests for VAS regions - VAS pressure.
By the way, note that there is a huge difference between VAS allocate and commit. Allocate just reserves a range of VAS addresses (64KB minimum) from the process space - physical memory (usually) isn't committed until the process actually tries to use the VAS page - at which point the OS maps a zeroed page of physical memory in (a demand zero page event).
The point is that without AWE, SQL Server has to allocate enough VAS to manage the whole buffer pool. By contrast, in AWE mode, the buffer pool pages are allocated from nonpaged memory (which doesn't use VAS and also can't be swapped to disk).
AWE uses VAS windows (4MB allocations) to map the nonpaged memory into the SQL Server VAS as required. The benefit is that the VAS 'window' onto the nonpaged memory is very small compared to the size of the nonpaged memory available. In SQL2K5, 4MB chunks of AWE memory can also be freed on demand if SQL Server detects VAS pressure.
Tim Walker (5/28/2009)
Or is it that 1GB isn't really enough to leave for the O/S?
Both. The OS kernel can struggle in 1GB of process space (not RAM!).
I hope that hasn't confused you further 😉
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2009 at 7:09 pm
If 64 bit you do not need to do /3gb switch in boot up and only on 32 bit is this correct.
Only on sql standard editions and not enterprise?
June 1, 2009 at 6:56 am
Paul, I have been away for a few days, hence the delay in my posting!
You have certainly not confused me further. Thanks very much for the detailed response, and for correcting the inaccuracies in my post. Your post makes things much clearer for me.
Thanks again.
Tim
.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply