3

For weeks I have been at more than 25k Page Life Expectancy, since last sunday on my UAT Server the Page Life Expectancy Dropped from ~25k to 400/500/600, I can't identify what cause this huge drop as this is a UAT Server no processes running during the weekend and nobody was connected.

Apart of this I identified that 95% of my buffer pages are allocated into one specific table on a clustered Index...

The application that access this table does a high amount of Adhoc queries without any parameterization (3rd Vendor Application, nothing I can touch...) I don't know if there is actually something I can do to leverage this RAM usage as they are consuming more than 50GB of RAM and I'm starting to face memory pressure BCHR <95% and low Page Life Expectancy!

Version:

Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64) 
Jan  7 2016 14:39:01 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Any help will be appreciated, Thanks!

Improve this question
4
  • 1
    Low PLE also means lot of I/O activity and since this started happening few days back you must find out what queries are causing most I/O's. This should be your first step. After that talk to your vendor about these queries. Forget BCHR and do not rely on it to gauge memory pressure. Plus it is suite normal for index to use chunk of memory in Buffer pool
    – Shanky
    Sep 30, 2016 at 14:25
  • I have the GHOST_CLEANUP process at 30mb/s 5%cpu since 2 weeks right now... If I add to this the users using the Application I can be at 40-50mb/sec on total and 15% cpu usage... I monitor (snapshots each 5m) the file DMVs and they are showing 60write operations per second and 1500 read operations as a peak load on the mdf file.
    – J1mmy
    Sep 30, 2016 at 14:50
  • Well I guess ghost cleanup is something which is I/O intensive you have already asked that question and know about it. So this makes it clear that problem is because of ghost cleanup task which would eventually stop after couple of days
    – Shanky
    Sep 30, 2016 at 14:54
  • I didn't know that low PLE could mean high I/O so something learnt today. Thanks a lot for your time Shanky, wish you a nice day! :D
    – J1mmy
    Sep 30, 2016 at 15:27

1 Answer 1

Reset to default
1

Your best option is going to be using Resource Governor. Just create a resource for that vendor app and throttle the memory down for it.

You just have to come up with what you are going to identify that workload with, you are allowed:

  • The ability to classify incoming connections and route their workloads to a specific group.
  • The ability to monitor resource usage for each workload in a group.
  • The ability to pool resources and set pool-specific limits on CPU usage and memory allocation. This prevents or minimizes the probability of run-away queries.
  • The ability to associate grouped workloads with a specific pool of resources.
  • The ability to identify and set priorities for workloads.
Improve this answer
1
  • Thanks for you answer Shawn, I was hoping that there was something else I could do to help this tool to not run out of RAM so quick, perhaps I will have to limit the amount of RAM some access patterns are consuming but this will drive to the application tu even run slower as it will have even less RAM.
    – J1mmy
    Sep 30, 2016 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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