Page life expectancy

Check if you have have "Lock pages in memory" right assigned for the SQL Server service account (how to: http://msdn.microsoft.com/en-us/library/ms190730.aspx). SQL Server service has to be restarted after you set this right.

i would expect these values to be between >=300  for normal performance .It should be >=600 for better performance.

To Brejk and others:
It's SQL server 2008 on a 64-bit system.
"Locking pages in memory is not required on 64-bit operating systems."

On a 64-bit platform, the AWE Enabled configuration option is present, but its setting is ignored. However, the Windows policy Lock Pages in Memory option is available, although it is disabled by default. This policy determines which accounts can make use of a Windows feature to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. It is recommended that you enable this policy on a 62-bit system

Please check how much the server memory, what is the maximum settings for sql server memory.

sp_configure

also check the performance counters
SQLServer:Memory Manager->Target Server Memory (KB)
SQLServer:Memory Manager->Total Server Memory (KB)

Here is another extract, with Target and tot server memory:

YYYY-MM-DD HH:MM Page        Target      Total
---------------- ----------- ----------- -----------
2009-12-16 16:50 740         359312      359312
2009-12-16 16:45 690         369108      359266
2009-12-16 16:40 390         359278      359278
2009-12-16 16:35 99          360264      360264
2009-12-16 16:30 139         371539      371539
2009-12-16 16:25 217         357319      357319
2009-12-16 16:20 271         369812      359251
2009-12-16 16:15 411         362516      362516
2009-12-16 16:10 120         360477      360477
2009-12-16 16:05 57          369487      369487
2009-12-16 16:00 125         362027      362027
2009-12-16 13:55 589         367494      357206

that means your sql server target server memory is not even 1gb?

The server has 4GB memory, and Max memory configuration of SQL server is 2147483647

You should enable Lock pages in memory on x64 too. Also, you should set the maximum server memory in SQL Server (for now your SQL Server can "eat" all the memory for its buffer cache). Otherwise there can be a battle for the memory on your server (OS and other apps vs. SQL Server).

Run DBCC MEMORYSTATUS on your SQL Server instance when the page life expectancy goes low and put the results here, please.

One more thing. If lock pages in memory is not set, the OS can flush the SQL Server's buffer cache whenever it needs (when a memory pressure occurs). And, 4GB is not a great amount of memory for a database server.

Yeah.. 4gb?. that means by default os will be using 2gb and remaining 2 GB will be used by other applications.

VeeNash,
Based on the target and total server memory, you say:
'that means your sql server target server memory is not even 1gb?'.
But we have 8gb of RAM (I thought it was 4 but it is 8).
What can be the reason it is so low?

Is SQL Server 64-bit edition? You mentioned it's on 64-bit OS, but we don't know if SQL is 64-bit too.

SQL server is 64 bit too.

Hi -

Can you please run the following SQL statement and share your results? SELECT @@VERSION and share the entire result set.

The target and total server memory is not even a GB. That number is measured in KB, unless you are doing the math and showing it in MB or missing numbers it looks like your SQL instance is using around 360GB according to perfmon.

So this is telling me that either there is some other large memory consumer on the machine (what else is installed on this box? What else is showing in task manager as consuming memory?), or there is something else going on here or perhaps even though you believe you are on x64 SQL Server, you are not. Do you run SSIS/SSAS/SSRS on this box as well? What else?

I would definitely agree with the suggestion to give the lock pages in memory rights, it can be necessary even in 64 bit environments.

You are going down the right road here looking at memory: lower page life expectancy is telling you that SQL Server pages are flushing out of memory more often than you would like so the life expectancy of a page is less than the suggested 5 minute minimum. This is a sure sign of memory pressure on the box and why you are only seeing 300MB or so in SQL is a puzzling question.

Can you attach a screenshot of the properties of the server? You can go into paint and hide any information that needs to be redacted like the registered to. Right click on my computer and go to properties. That's the screenshot I want to see in addition to the information from SELECT @@Version.

Also run the following:

SP_CONFIGURE 'Advanced', 1
GO
SP_CONFIGURE
and copy and paste the output of that into excel and attach it. This shows us your system settings. I am pretty sure there isn't anything worth redacting in here but go through it and make sure before attaching it. I want to see all of your system settings.

About the total memory page: isn't each page 8K? So 359312*8*1024 = 2.943.483.904 bytes, this is nearly 3GB. Correct? Is this a good value for a system with 8GB of RAM?

@@version:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
      Mar 29 2009 10:11:52
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Server properties:
System:
  Microsoft Windows Server 2003 R2
  Standard x64 Edition
  ServicePack 2
Computer:
  Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
  2.49 GHz, 7.75 GB of RAM

There is not running any of SSIS/SSAS/SSRS.

Performance monitor shows:
Physical memory (K):
  Total: 8.125.704
  Available: 591.344
  System Cache: 389.684.

The processes tab shows sqlservr.exe as the greatest consumer of Mem.Usage with 208.588K. Other processes are using below 46.000K of memory.

Some system settings:
name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max degree of parallelism           0           64          0            0
max full-text crawl range           0           256         4            4
max server memory (MB)              16          2147483647  2147483647   2147483647
max text repl size (B)              -1          2147483647  65536        65536
max worker threads                  128         32767       0            0
media retention                     0           365         0            0
min memory per query (KB)           512         2147483647  1024         1024
min server memory (MB)              0           2147483647  0            16

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max degree of parallelism           0           64          0            0
max full-text crawl range           0           256         4            4
max server memory (MB)              16          2147483647  2147483647   2147483647
max text repl size (B)              -1          2147483647  65536        65536
max worker threads                  128         32767       0            0
media retention                     0           365         0            0
min memory per query (KB)           512         2147483647  1024         1024
min server memory (MB)              0           2147483647  0            16

Select allOpen in new window

Target and Total server memory is measured in KB, not pages. SQL will generally only use the memory it needs but if you see page life expectancy continually getting lower that tells me that SQL may need more and I am a bit puzzled as to why it isn't taking it. What kind of query load are you experiencing? How large are your databases?

Task Manager is showing that you only have about 600MB available and about 400MB in use by the system cache.

What else is installed on this machine? Can you grant the lock pages in memory permission and restart the server?

Nothing else is running on this machine. Attached a list of all processes.
Before I  grant the lock pages in memory permission, I need to be sure this will solve the problem, because I have to restart the server.
Processes.bmp

Anything in your SQL Server error log about memory  being paged out? Anything about memory in general? Anything in your system event log or application log?

Also what about the how busy and how large questions?

It is possible that something is using or reserving more memory but you don't see it in Task Manager. In fact with lock pages in memory, SQL won't show all the memory it is using in task manager, which is why I look to the memory counters you looked at already.

Is this a VMware guest host or a host of other VMware guest hosts? I see those vmware processes.

take a look at perfmon and look at the process object and all instances and the private bytes and working set counters.  You might want to look at them separately (Private Bytes then working set) or look at the report view.. Who are the top reported and what is their average or max after running for a bit?

elementary explanation on working set/private bytes here: http://technet.microsoft.com/en-us/library/cc958292.aspx

What is your paging file size set to out of curiosity also?

Sorry.. Working set measures bytes, not pages.. Mistyped there.

You can even set the scale ridiculously high (50,000/100,000) and then look at a line graph and look at the top items. The _Total counters will be highest. What are the values of the next counters. You can either report the raw values here or hit google and ask for the value in MB or GB if you want.. "10100101123Bytes in GB"

The highest value are for sqlsrv. All others are minimal.

The query
select
      total_physical_memory_kb / 1024 as phys_mem_mb,
      available_physical_memory_kb / 1024 as avail_phys_mem_mb,
      system_cache_kb /1024 as sys_cache_mb,
      (kernel_paged_pool_kb+kernel_nonpaged_pool_kb) / 1024
            as kernel_pool_mb,
      total_page_file_kb / 1024 as total_page_file_mb,
      available_page_file_kb / 1024 as available_page_file_mb,
      system_memory_state_desc
from sys.dm_os_sys_memory

returns:
phys_mem_mb=3839
avail_phys_mem_mb=157
sys_cache_mb=145
 kernel_pool_mb=95
total_page_file_mb=7682
available_page_file_mb=3785
system_memory_state_desc=Physical memory usage is steady

Everything seems OK. So what can be the reason of the pagelife fall back?
 

So that counter isn't showing you anything about SQL Server using memory but it is showing you have a bit less than 4GB of Physical Memory according to SQL's view of the system information here. With 157 available Megabytes. Your page file is about8GB with about 4GB available.

Your memory doesn't seem to match up with what you and your machine thinks you have according to this. Is this a host of other vmware instances?

You say SQL was the highest of the consumers of private bytes/working set. Can you just report the top 5 and their raw numbers? Also what is the current total and target memory for your SQL Server instance?

Low Page life expectancy comes because you have to dump SQL's pages out of memory to read new pages in. That happens because your query demands and pattern isn't kept up with by the memory on your machine. Either this can signify you need more memory, you have queries that are doing more reads than they should or a combination thereof.

New try to attach the pictures with private bytes and working set counters;
WorkingSet.bmp

And private bytes:
PrivateBytes.bmp

So the working set is about 377MB
Private bytes is about 585MB

SQL just isn't using that much memory. Yet you don't have a lot available. Can you Switch to a histogram and increase the scale to 50,000 and share the numbers for the other large consumers?

The SQL instance is a Virtual machine with 8GB of RAM. See picture attached.
Nothing else is running on the server.
Also attached the histogram. Each time the highest value is 'Total', the lower one is sqlsrv.
And sorry, but the query was of another server. Here are the corectresults:
phys_mem_mb=7935
avail_phys_mem_mb=618
sys_cache_mb=484
kernel_pool_mb=76
total_page_file_mb=11585
available_page_file_mb=4656
system_memory_state_desc=Available physical memory is high
Full.bmp

Experts,

My question: About the total memory page: isn't each page 8K? So 359312*8*1024 = 2.943.483.904 bytes, this is nearly 3GB. Correct? Is this a good value for a system with 8GB of RAM?
Experts answer: 'Target and Total server memory is measured in KB, not pages.'

Sorry guys, but I'm pretty sure that the 'target pages' are expressed in 8K pages.
See: http://msdn.microsoft.com/en-us/library/aa337525.aspx
A buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache is divided into 8-KB pages.
See also info about DBCC Memorystatus: '... describes the distribution of 8 KB buffers in the buffer pool.'

One more key item came to mind here.. This is a VMware guest host. I bet you are having issues with your VMWare environment and memory limits or memory reservations. The Operating System is set to have 8GB but it is really only using a lot less.

Sometimes VMWare will limit memory of hosts either because of configuration options or during performance constraints from other guest hosts running. Is the underlying VMWare infrastructure ESX here? How many other hosts do you have on it? I would definitely look at your virtualization settings and memory reservations and make sure this box isn't being limited and reboot the virtual machine.

Have you looked at the VMWare side of things? Or answers to the other questions? Just curious if this is still an issue or if it is now resolved.

The databases are 223GB, 3900MB, 1600 MB, 770MB 504MB, 270MB, 250MB, and 4 database lower than 20MB.
The virtual machine is rebooted and the Lock pages in memory is enabled (stated into sql log).
Pagelife expectancy yesterday was 80 times (on 288 checks) below 300.

Erde - Ok. Can you please look at how the VMWare environment is configured. Like I commented previously - when a VMWare host (Your SQL Server is a "guest host" on a VMWare server) is allocated an amount of memory that is what shows when you view your memory settings. But, if the VMWare environment is limiting your memory because of a limit/reservations even though it "looks" like you have x amount of memory you may not really be allocated that much.

Looking at how much your host is reporting it has, how little SQL is using and how little everything else is using, I am fairly confident that this is where your problem lies.

MikeWalsh, thanks for your help. I think we will assign more memory to the VM server and see what it will do.
If necessary, I wil open a new post.
Thanks.

Thanks for your help and information.

No problem. I would not just assign the memory but also look at your limits and reservations to make sure you aren't limiting it some other way.

Glad I could help.