SQLBits 2015

What Happened? Exploring the Plan Cache

In this session, we'll explore SQL Server's plan cache, including techniques for discovering what plans are in cache, how often they've been run, and whether they contain any sub-optimal operators. Knowing what's happened is the first step in tuning.
Using one of the various tracing capabilities of SQL Server, is the best way to keep track of what is going on in your SQL Server to cause performance problems. But if you haven't set up any tracing, what can you do to find out what problematic queries might be running? In this session, we'll explore SQL Server's plan cache, from the way it is managed in memory, to the tools and techniques for discovering what plans are in cache, how long they've been there, how often they've been run, and whether they contain any suspected sub-optimal operators, such as table scans and hash joins. Knowing what's happened is the first step in knowing what needs to be fixed.