A script I use in every Health Check: get the compiled plan with parameters from cache, so you can see what values the plan was built for.
Capturing Actual Execution Plans From a Script With SET STATISTICS XML
Capture actual plans with SET STATISTICS XML, save the extra result safely, inspect runtime fields, and compare last-known plan statistics.
Many-to-Many Merge Join and the Worktable in STATISTICS IO
Diagnose a many-to-many merge join and its worktable, verify true uniqueness, and compare join choices without changing legitimate matches.
How to Compile Queries and Not Execute Them? – Interview Question of the Week #219
How do you compile queries and not execute them? SET NOEXEC ON validates the syntax without running the statement, as this simple SSMS example shows.
Wide Update Plans: What Every Extra Index Costs a Write
Inspect wide update plans, compare index-maintenance work with narrow updates, and read per-index counters without misreading STATISTICS IO.
SQL SERVER – List Query Plan, Cache Size, Text and Execution Count
A workshop attendee asked how to see the query plan, cache size, text and execution count for queries. This simple query lists all of them from the plan cache.
Eager Index Spool: When the Plan Builds Its Own Index
Investigate an eager index spool, read seek keys and output, and compare a supporting index with the cost of rebuilding temporary storage.