A common but tricky issue to identify in SQL Server is when something called "parameter sniffing" goes bad. In this video, Redgate's Kendra Little shows how to identify if a query has an unstable execution plan in SQL Monitor, where to find the parameters which were "sniffed" for each query plan, and gives tips on where to start to resolve the issue.
In this video:
00:00 High level overview of parameter sniffing
00:40 Navigating to top queries on the instance and sorting by logical reads
01:20 Finding the query fragment and calling stored procedure/function/trigger (if applicable)
01:50 Reading the query history graph and comparing plan changes and performance
02:53 Examining the "fast" plan and identifying the compiled ("sniffed") parameters
04:10 A warning in the plan about indexes, and how to interpret it
05:24 Examining the "slow" plan and identifying it's compiled ("sniffed") parameters
06:14 Comparing the IO behavior in the slow plan
07:13 Summing up why the "slow" plan is behaving differently
07:45 Digging into "output" columns in the slow plan to confirm index changes that might help
08:35 Big picture description of parameter sniffing and options to stabilize plans when it goes "bad"
