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
SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
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)
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
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
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
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
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.
From The output of performance data collector as requested for above counters
Below are some notable points
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
Memory grants pending was
Free List stalls/Sec was
always zero. Which means no request had to wait for free pages
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.
- it would be incorrect to say that its a memory pressure, its only that particular process is creating scenario which seems like memory pressure.