July 12, 2016 at 11:02 am
Our production server (VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes.
Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.
Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400.
What's the explanation for this ?
I though PLE & BCHR had a linear relationship (i.e. they increase or decrease together)
On other VMs with larger databases and lot more RAM, both BCHR & PLE are high.
July 12, 2016 at 11:20 am
sqld-_-ba (7/12/2016)
Our production server (VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes.Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.
Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400.
What's the explanation for this ?
I though PLE & BCHR had a linear relationship (i.e. they increase or decrease together)
On other VMs with larger databases and lot more RAM, both BCHR & PLE are high.
Quick suggestion, have a look at Page Life Expectancy isn’t what you think… By: Paul Randal[/url]
😎
July 12, 2016 at 11:26 am
sqld-_-ba (7/12/2016)
Our production server (VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes.Ideally would like to increase the RAM to 64GB or 128GB, but need to 'prove' to team that it's required.
Buffer Cache Hit Ratio (BCHR) is 99.9%, but Page Life Expectancy (PLE) is only ~400.
What's the explanation for this ?
I though PLE & BCHR had a linear relationship (i.e. they increase or decrease together)
On other VMs with larger databases and lot more RAM, both BCHR & PLE are high.
tempdb usage being "high" has nothing to do with BCHR/PLE.
If tempdb usage is high due to SORT/HASH/other memory-consuming stuff getting spooled to disk then you need more RAM (or need to improve your queries).
You can find various queries online to see which are using tempdb. sp_whoisactive can also show it. I think you may need to go down to Extended Events to see specifics on HASH/SORT etc tempdb usage. I note that more detail was just added to execution plans in the lastest updates to SQL Server 2012/14 and 16 has it baked in.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2016 at 11:32 am
What are your wait statistics? What is the server actually running slow because of? That's one of the most important things to know about.
"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
July 12, 2016 at 11:50 am
@Eirikur, one article says PLE is not what it seems, and another says BCHR is not what it seems 🙂 .. just like 'latest scientific studies show X is both good and bad'
@TheSQLGuru, yes I'm able to narrow down which query is using tempdb with whoisactive, but this is 3rd party code and we cannot modify it. I just need to be able to prove to Systems guys that we're experiencing memory starvation.
@Grant, see - http://imgur.com/a/I8ZQn - for wait stats and common memory-related perf counters from the box.
Right now, CPU is maxed at 100%, there is a lot of tempdb usage and physical reads and writes, and tons of compilations/sec (~95% of all batch requests)
I've run several scripts industry-accepted from BrentOzar, SQLSkills etc. but there are so many, some are outdated, some don't work as well for VMs vs Physical etc. so I do not have one GO-TO query to instantly prove it's a CPU or Memory issue,
July 12, 2016 at 12:20 pm
Here are a few other memory related dmv queries you can also run, see the comments for the preferred values:
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Outstanding] FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);
-- Memory Grants Outstanding above zero for a sustained period is a very strong indicator of memory pressure
SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending] FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure
July 12, 2016 at 12:40 pm
@RVarn , there is no external pressure . this is a SQL-only box (no apps), with 32GB ram on OS and 28GB max mem for SQL
There is no 'internal pressure', both values are zero
Yet, Memory Grants pending is ~40 consistently.
Which one is a better indicator of memory starvation ?
July 12, 2016 at 12:57 pm
sqld-_-ba (7/12/2016)
@RVarn , there is no external pressure . this is a SQL-only box (no apps), with 32GB ram on OS and 28GB max mem for SQLThere is no 'internal pressure', both values are zero
Yet, Memory Grants pending is ~40 consistently.
Which one is a better indicator of memory starvation ?
Memory grants pending. You definitely have a problem. With third party systems a plan guide is often only recourse, although you can often "void your warranty" by adding some indexes and make the system FLY. Have a script that drops them if needed prior to calling for support or applying a patch. Then re-add them. I have helped clients do this more than a few times.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2016 at 3:49 am
I'm with Kevin on this one - Grants pending = 40 does indicate an issue ( I have seen this coupled with resource semaphore waits)
PLE does get mis-interpreted if you are on NUMA systems, people usually look at the wrong counter, i.e. manager over node.
Also I personally wouldn't use BCHR as by the time it dips your performance is most likely already poor.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply