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!
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!