August 4, 2014 at 10:47 am
Normally I would not consider high page life expectancy a bad thing but I have two servers reporting a PLE of over 4,000,000. Before the servers reported this high number the average was around 5,000. I have not added more memory to the system the only thing I have changed is added a few missing indexes. I am finding it very hard to believe that a couple indexes would make this jump so much!
Is there anything else I should be checking to validate that it is indeed over 4 million and not a bug? Even after a server restart the number jumps right back up. The Buffer Cache Hit is averaging around 99%
August 4, 2014 at 11:59 am
You might just be seeing high PLE, but it could also be related to running on a NUMA architecture[/url]. Instead of just reading the average, get the specific values for your CPUs and see if maybe the numbers aren't different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2014 at 12:53 pm
I forgot to mention in my first post. This is a virtual machine not running on NUMA architecture.
August 5, 2014 at 3:48 am
Some of the memory and CPU measurements within a VM become problematic. Again, I'd drill down to the specifics instead of using the average, but you might also want to look to the documentation on your hypervisor to see what it has to say about your current memory usage.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2014 at 8:22 am
I am monitoring the servers and DBs with SQLSentry, the numbers I am reporting are actual not averages. I also ran queries against dmv's and perf-mon counters assuming what SQLSentry is reporting was wrong but they match.
Since yesterday one of the servers went back to normal. It just so happens to be the server that was restarted over the weekend. We have a scheduled maintenance window this weekend for patches, perhaps it will also fix it self. I was just hoping to figure it out what was going on before it gets restarted.
vCenter is reporting 16GB consumed, which is what we have allocated to the server. 10GB is assigned to SQL and the rest to the OS.
Here are some memory stats:
----------------------------------------------------------------------------------------------------
Memory usage details for SQL Server instance (10.50.4000.0 - X64) - Enterprise Edition (64-bit))
----------------------------------------------------------------------------------------------------
--------------------------------------
Memory visible to the Operating System
Physical Memory_MB Physical Memory_GB Virtual Memory GB
--------------------------------------- --------------------------------------- ---------------------------------------
16384 16 8192
-------------------------------
Buffer Pool Usage at the Moment
BPool_Committed_MB BPool_Commit_Tgt_MB BPool_Visible_MB
--------------------------------------- --------------------------------------- ---------------------------------------
10240.000000 10240.000000 10240.000000
---------------------------------------------------------------------------
Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
10485760 10240.000000 10.000000000
-------------------------------------------------------------
Memory needed as per current Workload for SQL Server instance
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
10485760 10240.000000 10.000000000
------------------------------------------------------------------------------
Total amount of dynamic memory the server is using for maintaining connections
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
14680 14.335937 0.013999938
------------------------------------------------------------
Total amount of dynamic memory the server is using for locks
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
18992 18.546875 0.018112182
----------------------------------------------------------------------------
Total amount of dynamic memory the server is using for the dynamic SQL cache
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
5792 5.656250 0.005523681
-------------------------------------------------------------------------
Total amount of dynamic memory the server is using for query optimization
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
3408 3.328125 0.003250122
-------------------------------------------------------------------------------
Total amount of dynamic memory used for hash, sort and create index operations.
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
0 0.000000 0.000000000
------------------------------------------
Total Amount of memory consumed by cursors
Mem_KB Mem_MB Mem_GB
-------------------- --------------------------------------- ---------------------------------------
11176 10.914062 0.010658264
-------------------------------------------------------------------------
Number of pages in the buffer pool (includes database, free, and stolen).
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
1310720 10485760.000000 10240.000000000
---------------------------------------
Number of Data pages in the buffer pool
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
909000 7272000.000000 7101.562500000
---------------------------------------
Number of Free pages in the buffer pool
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
338521 2708168.000000 2644.695312500
-------------------------------------------
Number of Reserved pages in the buffer pool
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
0 0.000000 0.000000000
-----------------------------------------
Number of Stolen pages in the buffer pool
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
63199 505592.000000 493.742187500
---------------------------------------------
Number of Plan Cache pages in the buffer pool
8KB_Pages Pages_in_KB Pages_in_MB
-------------------- --------------------------------------- ---------------------------------------
7976 63808.000000 62.312500000
-----------------------------------------------------------------------------------------------
Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references
Page Life in seconds PLE Status
-------------------- ------------------
4287454 PLE is Healthy
--------------------------------------------------------------
Number of requests per second that had to wait for a free page
Free list stalls/sec
--------------------
12820
-----------------------------------------------------------------------------------------------------------------
Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed
Checkpoint pages/sec
--------------------
17748725
------------------------------------------------------------------------
Number of buffers written per second by the buffer manager"s lazy writer
Lazy writes/sec
--------------------
279166
--------------------------------------------------------------
Total number of processes waiting for a workspace memory grant
Memory Grants Pending
---------------------
0
----------------------------------------------------------------------------------
Total number of processes that have successfully acquired a workspace memory grant
Memory Grants Outstanding
-------------------------
0
November 17, 2016 at 7:25 am
I have the same problem (virtual machine), PLE values:
393
393
393
4294925
4294940
4294956
440
440
440
I solve the problem with this powershell script:
$server = "Server1"
& relog Server1_XXXX.blg -f csv -o logfile.csv
($csv = Import-Csv logfile.csv -Delimiter ',') | ForEach-Object{
if([convert]::ToInt32($_."\\$server\SQLServer:Buffer Manager\Page life expectancy", 10) -gt 100000)
{
$_."\\$server\SQLServer:Buffer Manager\Page life expectancy" = 1000
}
}
$csv | Export-Csv logfile_fixed.csv -Delimiter ',' -NoType
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply