4

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

Server: SQL server 2008R2

Databases : System and 2 user DB's with max size of DB being 54 GB

RAM: 16 GB

Max server system memory: 12.24 GB

Once i got a hint for memory pressure i used RAMMAP to find memory usage and found private process using 13.8 GB indicating a further doubt on lower memory pressure:

I analysed and found that one of the User DB A was using 8 GB RAM as per the blog from Paul Randal:Additionally i analysed for a period of time and saw tempdb using at an avg of 3-4 GB of RAM

Then with further investigation i went and found that in that database A there was a table having a clustered index consuming on average 6-6.5 GB of the memory.( Fragmentation is not an issue as its been checked)

Now from here i am unsure how to proceed? should i go and create the missing indexes or can drop the unused? or there is something else i need to dig in before making any conclusion.

Please suggest!

  • 1
    If an index is truly unused, then it's logically not contributing to buffer pool usage. Adding more indexes is not likely to help this scenario unless they satisfy all existing queries that use the CIX scan and are significantly smaller (not all indexes are that much smaller than the table itself). The real problem is that you have a server with 16 GB of RAM and data that is obviously larger than that. So you should either (a) add memory or (b) adjust your PLE alerts to be more relevant for your server and workload. – Aaron Bertrand Jan 7 '15 at 19:14
  • Aaron@ Yes that;s right. However i went on to analyse on tempdb as it was also consuming around 4-5 GB on average and found that being 16 core processors there is only one tempdb mdf and one ldf. Per good configuration should i also consider the tempdb files to be equal to no of processors ? – KASQLDBA Jan 7 '15 at 20:04
  • 1
    NO. tempdb being big is not necessarily a problem (unless it is constantly auto-growing and you are fighting that by shrinking it). You should consider multiple files if you are actually observing contention in tempdb. If you are, then consider starting at some fraction of the number of processors. – Aaron Bertrand Jan 7 '15 at 20:25
  • 1
    Make sure you have an even number of tempdb data files, all set to the exact same size and the exact same growth, and you should only ever have one log file. You should enable trace flag 1118 and consider trace flag 1117 (as long as you don't have multi-data-file user databases that are meant to grow at different rates). For more info see Kendra's post. – Aaron Bertrand Jan 7 '15 at 20:28
  • 1
    What I said is there were some memory leaks fixed in SP release, upfront I cannot tell you whether you are facing it but its always better to be on Latest S because you are under support( extended in this case) and bugs if any are fixed. You must upgrade to latest SP. Again dont rely on JUST THE PLE. There are other counters as well. – Shanky Jan 8 '15 at 18:04
4

First and foremost thing, SQL Server in picture is SQL Server 2008 R2 RTM which is not supported by Microsoft in any way please apply SQL Server 2008 R2 SP3 to at least get extended support

I analyzed and found that one of the User DB A was using 8 GB RAM

I don't think there is any issue because of this, this is totally normal looking at this you cannot draw any conclusion.

A there was a table having a clustered index consuming on average 6-6.5 GB of the memory

I guess this table is biggest table of database which is consuming most part of buffer pool. I cannot upfront say about why table is holding so much memory so again you should first apply SP3 to rule out any possibility of leaks and then move with troubleshooting.

For one of my server with below configuration i am continuously receiving alerts for low PLE which ranges between 20- 400 and an average of 90-100 in a day:

For a system with 12 G RAM PLE should be around 900. But since you say its always around 300-400 which makes me thing SQL Server has to work hard and move pages frequently out of memory to disk because of memory pressure. To confirm memory pressure you have to rely on other counters not just PLE you can open perfmon and add following counters. It would be best Create a Data Collector Set to Monitor Performance Counters

  1. SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

  2. SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

    ( Ideally Target value should be less than or equal to Total)

  3. Page reads/sec – Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design

  4. Free Pages – Total number of pages on all free lists (free lists track all of the pages in the buffer pool that are not currently allocate to a data page, and are therefore available for usage immediately). Undoubtedly this value should be high

  5. Page Life Expectancy – Number of seconds a page will stay in the buffer pool without references> if you have NUMA system analyze PLE for each node as mentioned in this article

  6. Free List Stalls/sec – Number of requests per second that had to wait for a free page. Ideally stalls should be as zero or as minimum as possible

  7. SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: If you see a non zero value of memory grant pending with Low PLE and High free List stalls you definitely have a memory pressure and should consider providing more RAM.

Please note: the value for above counters should be collected for at least 3-4 hours and when load on system is relatively very high. If possible before collecting the values please flush buffer cache , although I am not saying you to do it I know for Prod system it would not be possible. If you would produce the values I could analyze it for you.

I have no idea about workload of the system and just giving you option how to proceed. You should also analyze costly queries. Look for one that involves hash joins and sorts.

A bad query with does lots of joins and scans on large table has all capacity to bring counters like PLE,memory grants, free list stalls to value which make syou believe its memory pressure, ya it is but the cause is poor query and missing indexes and incorrect join. You should also consider this.

Edit:

From The output of performance data collector as requested for above counters

enter image description here

Below are some notable points

  1. Target server memory and total server memory remained constant and there value were equal throughout the data collector process( from 10 AM in morning to 3 PM in afternoon). which points to fact that SQL Server database engine was happy with its current memory requirement

  2. Memory grants pending was always zero.

  3. Free List stalls/Sec was always zero. Which means no request had to wait for free pages

  4. There was considerable decrease in PLE from 2:15 PM to 3:18 PM and at the same time pages read/sec were very high which makes me believe some query/process started after 2:00 PM which required too much pages in memory and hence causing flurry of activity in Buffer pool.

You need to find out what query is running after 2:00 PM and it was still running so it makes me think there is process.job starting at 2:00 PM. This process/job/query is trying to read too many pages which is causing lazywriter to flush too many pages because query is requesting space for such pages

It can be that such query is missing index, can be that its creating a bad plan due to skewed statistics, it can be that query needs to be written to get/read only subset of data not whole data.

  1. it would be incorrect to say that its a memory pressure, its only that particular process is creating scenario which seems like memory pressure.
  • Thanks Shanky for pitching in: I've cleared cache with help of DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.. Have set the collector sets with counters defined above. 8 AM to 8 PM EST being the heavy load duration, would run for 4 hours and let you know the outputs – KASQLDBA Jan 9 '15 at 13:44
  • Shanky@ i have the data collected.. i am sorry but how should i send the data file or attach it here, i dont see any such option – KASQLDBA Jan 9 '15 at 20:46
  • Okay i got it, here you go, please see if you can access below link: onedrive.live.com/… – KASQLDBA Jan 9 '15 at 20:55
  • Can you also upload errorlog for today's date. What is output of select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB from sys. dm_os_process_memory – Shanky Jan 9 '15 at 22:58
  • 1
    Thanks Shanky@ I will use the inputs listed above to conclude to a point.Also there is this job running every hour executing a SP on that DB "A" in question, so i think i need to get that reviewed by developer. – KASQLDBA Jan 15 '15 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.