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!

Community
  • 1
J1mmy
  • 550
  • 1
  • 8
  • 21

1 Answers1

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.