PLE is low most of time!

Question

PLE is low most of time!

Tac11

SSCertifiable

Points: 6976
More actions
January 31, 2021 at 2:01 pm

#3839315

My prod server (physical) has Total physical memory around 375GB and "max memory" setup 300+ GB. But I see PLE low all the time as low as 50 sec.! When Tried to dig into using "sys.dm_os_buffer_descriptors" found one clustered index (PK) taking 50-60GB as Cached, other index low cached sizes, may be another 5-6GB combined.
My question is, where did all memory go since buffer pool is size of 300+GB. Where should I be looking into? What's the best way to bring PLE up? Any suggestions?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Have you checked so see which code is/are doing full table (Clustered Index or Heap) scans? That was our problem a long time ago. We had to fix the code and add some of the right kind of indexes. Some of the things we fixed in the code was non-SARGable predicates and a whole bunch of things that were passing criteria as NVARCHAR() for lookups (WHERE clause, mostly) on VARCHAR() columns, which causes the entire column(s) to be read and converted prior to the lookup. You can also have a similar problem with JOIN columns (and WHERE IN, etc). Such implicit casts are real memory hogs and performance problems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tac11 SSCertifiable Points: 6976 More actions · Answer 2

Thanks Jeff for your kind reply. One think in my mind is even if there is/are table or index scans, shouldn't 'sys.dm_exec_query_stats' shows you very high on columns like '_grant_kb' ? which dmv or query should I run to add up all memory being used? Thanks

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 3

My question is, where did all memory go since buffer pool is size of 300+GB. Where should I be looking into?

At a basic level, SQL Server prefers to keep 75% of the buffer pool set aside for query execution. Resultsets must be assembled in memory before they can be sent to a client. Sorts are particularly memory-heavy. If you run out of this memory, then query work is performed by writing and modifying resultsets in tempdb before sending them to the client. Writes are more expensive than reads, so it's better to use memory for query execution than for caching data pages.

Eddie Wuerch
MCM: SQL

Tac11 SSCertifiable Points: 6976 More actions · Answer 4

Tac11

SSCertifiable

Points: 6976

February 1, 2021 at 8:00 pm

#3839803

Thanks Eddie for your kind reply.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Glenn Barry has a cool set of diagnostic scripts by SQL Server Edition at the following URL...

https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

Since you posted on a 2017 forum, you should find his 2017 version of his diagnostics on that page. He has a ton of snippets in that code that deal with "memory" in that code. Search for "memory" in the code. Just a bit about his code writing style when it comes to comments. He has a comment above each snippet of code and another below each snippet of code. Remember to look at the code above the latter.