Fixing Page Life Expectancy Steve Hood Email SteveSimple

Fixing Page Life Expectancy Steve Hood Email: Steve@Simple. SQLServer. com Blog: Simple. SQLServer. com

What is PLE? • Duration in seconds data stays in memory References • Monitor PLE in OS Perf Counters

It’s just a counter • May always be low on OLAP environments • Are you waiting on PAGEIOLATCH_SH? • Monitor your Wait Stats • Faster disks can help compensate References • Monitor your Wait Stats

Ideal Value • Common advice of PLE > 300 is outdated • Cache Size In GB / 4 * 300 • Best generic formula References • Jonathan Kehayias discusses the plan cache and PLE • Comment discussion with Brent Ozar is as good at the article • Jonathan Kehayias’s free book: Troubleshooting SQL Server

Increase PLE • More memory • Query Tuning • Query Justification • Indexing Changes • Data Cleanup

More Memory • 64 GB in 2008 and 2012 Standard • 128 GB in 2014 Standard • Unlimited in Enterprise • 768 GB is reasonable per physical box

What DBs are in cache? References • Script: Cache. Size. By. DB

What Indexes are in Cache? References • Script: Cache. Size. By. Index (in speaker notes)

Finding Queries to Tune • Look in the Proc Cache (not 100% reliable) • Most Expensive Queries • Index Usage in Proc Cache • Server-Side Trace or Extended Events

Proc Cache Limitations • Data since last compile • Can monitor it better than that • Only cacheable plans

Most Expensive Queries • Uses Proc Cache • For PLE focus on Physical Reads References • Query Stats Monitoring – know your stats beyond your current cache • Script: Most Expensive Queries (in speaker notes)

Index Usage in Proc Cache • Uses Proc Cache • Goes along with “What indexes are in cache” • Gives Estimated IO cost • Gives Seek and Scan Predicates References • Script: Index Usage in Proc Cache (in speaker notes) • Script: Table Usage in Proc Cache (in speaker notes)

Server-Side Trace or EE • Duration over X seconds (5) • Reads over X (100, 000) References • Tracing Introduction – Creating your first Server-Side Trace • Reading Traces – Querying your Server-Side Trace • Erin Stellato Making the Leap from Profiler to Extended Events

How To Tune • Beyond the scope of this presentation • Execution Plans – Grant Fritchey • Google SARGability • Brent. Ozar. com • SQLskills. com

Appropriate Queries? • Did this query need to run? • …in prod? • …during peak hours?

Indexes – Drop Unused • Data modifications pull data into cache • Index maintenance pulls data into cache References • Indexes – Unused and Duplicated

Indexes – Remove Dupes • First couple key fields match • Two indexes that could almost replace each other References • Indexes – Unused and Duplicated

Indexes – Compression • Enterprise only • Some don’t compress at all • Some compress by 90%

Data Cleanup • Any data has potential to use cache • If you can purge 25% of your data…

Fixing Page Life Expectancy Steve Hood Email: Steve@Simple. SQLServer. com Blog: Simple. SQLServer. com