[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Page life expectancy

Posted on 2009-12-16
37
Medium Priority
?
3,890 Views
Last Modified: 2012-05-08
Sometimes the Page life expectancy counter of our server is very low. We have many fluctuations. Is this normal?
What can be the reason why there are so much fall backs below the advised 300? We have no memory problem, and also only missing indexes with to much included columns.
This is an example of the fluctuations during a few hours:
YYYY-MM-DD HH:MM cntPage
---------------- -----------
2009-12-16 11:35 374
2009-12-16 11:30 175
2009-12-16 11:25 9
2009-12-16 11:20 277
2009-12-16 11:15 624
2009-12-16 11:10 538
2009-12-16 11:05 238
2009-12-16 11:00 8
2009-12-16 10:55 930
2009-12-16 10:50 659
2009-12-16 10:45 358
2009-12-16 10:40 182
2009-12-16 10:35 198
2009-12-16 10:30 10
2009-12-16 10:25 572
2009-12-16 10:20 272
2009-12-16 10:15 246
2009-12-16 10:10 172
2009-12-16 10:05 8
2009-12-16 10:00 274
0
Comment
Question by:erde
  • 16
  • 12
  • 5
  • +1
37 Comments
 
LVL 18

Expert Comment

by:brejk
ID: 26062423
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.
0
 
LVL 7

Expert Comment

by:VeeNashi
ID: 26062515
i would expect these values to be between >=300  for normal performance .It should be >=600 for better performance.
0
 
LVL 2

Author Comment

by:erde
ID: 26062608
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."
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 7

Expert Comment

by:VeeNashi
ID: 26063424
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
0
 
LVL 7

Expert Comment

by:VeeNashi
ID: 26063482
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)
0
 
LVL 2

Author Comment

by:erde
ID: 26063840
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
0
 
LVL 7

Expert Comment

by:VeeNashi
ID: 26064165
that means your sql server target server memory is not even 1gb?
0
 
LVL 2

Author Comment

by:erde
ID: 26065653
The server has 4GB memory, and Max memory configuration of SQL server is 2147483647
0
 
LVL 18

Expert Comment

by:brejk
ID: 26069849
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.
0
 
LVL 18

Expert Comment

by:brejk
ID: 26069854
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.
0
 
LVL 7

Expert Comment

by:VeeNashi
ID: 26074466
Yeah.. 4gb?. that means by default os will be using 2gb and remaining 2 GB will be used by other applications.
0
 
LVL 2

Author Comment

by:erde
ID: 26095297
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?
0
 
LVL 18

Expert Comment

by:brejk
ID: 26095337
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.
0
 
LVL 2

Author Comment

by:erde
ID: 26098795
SQL server is 64 bit too.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26125493
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.

0
 
LVL 2

Author Comment

by:erde
ID: 26130853
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

Open in new window

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26131488
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?
0
 
LVL 2

Author Comment

by:erde
ID: 26132044
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
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26132304
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?



0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26132365
Sorry.. Working set measures bytes, not pages.. Mistyped there.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26132385
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"
0
 
LVL 2

Author Comment

by:erde
ID: 26136754
The highest value are for sqlsrv. All others are minimal.
0
 
LVL 2

Author Comment

by:erde
ID: 26137139
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?
 
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26137336
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.

0
 
LVL 2

Author Comment

by:erde
ID: 26137361
New try to attach the pictures with private bytes and working set counters;
WorkingSet.bmp
0
 
LVL 2

Author Comment

by:erde
ID: 26137368
And private bytes:
PrivateBytes.bmp
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26137415
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?

0
 
LVL 2

Author Comment

by:erde
ID: 26137800
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
0
 
LVL 2

Author Comment

by:erde
ID: 26138065
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.'
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 26138413
Yes you are right about 8KB page and the math. Which counter were you showing the target and total from? If you are showing the SQLServer Memory Target Server Memory and Total Server Memory that is in KB... http://msdn.microsoft.com/en-us/library/ms190924.aspx

What counter were you showing referring to the Target and Total???

Also looking at your Private Bytes and Working set and doing the math it seems to line up with the analysis in terms of KB.

Again, can you please tell me how large your databases are and what kind of activity you are experiencing?  The odd thing here is that you just don't have a lot of memory that I can see in use but your available memory isn't so high. I wonder how this will look after a reboot.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26138522
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26161677
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.
0
 
LVL 2

Author Comment

by:erde
ID: 26178358
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26179697
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.
0
 
LVL 2

Author Comment

by:erde
ID: 26213469
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.
0
 
LVL 2

Author Closing Comment

by:erde
ID: 31666721
Thanks for your help and information.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 26213521
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question